Links

MSCEWI2077

RESET WHEN clause is not supported in this scenario due to its condition

Severity

Medium

Description

SnowConvert currently only supports RESET WHEN clauses with binary conditions (<=, >= or =). Any other type of condition, such as IS NOT NULL, the RESET WHEN clause will be removed and an error message will be added since it is not supported in Snowflake.

Example code

Input code:

SELECT
account_id,
month_id,
balance,
ROW_NUMBER() OVER (
PARTITION BY account_id
ORDER BY month_id
RESET WHEN balance IS NOT NULL
ROWS UNBOUNDED PRECEDING
) as balance_increase
FROM f_account_balance
ORDER BY 1,2;

Output code:

SELECT
account_id,
month_id,
balance,
ROW_NUMBER() OVER (
PARTITION BY account_id
--** MSC-ERROR - MSCEWI2077 - RESET WHEN CLAUSE IS NOT SUPPORTED IN THIS SCENARIO DUE TO ITS CONDITION **
ORDER BY month_id
ROWS UNBOUNDED PRECEDING
) as balance_increase
FROM f_account_balance
ORDER BY 1,2;

Recommendations