MSCEWI2077

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

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

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.

This error message also appears when the RESET WHEN condition references an expression whose definition was not found by the migration tool. Currently, the tool supports the alias references to a column that was defined in the same query.

Example code

Condition is not binary

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 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 account_balance
ORDER BY 1,2;

Condition expression was not found

Input code:

SELECT
    account_id,
    month_id,
    balance,
    ROW_NUMBER() OVER (
        PARTITION BY account_id
        ORDER BY month_id
        RESET WHEN balance <= not_found_expresion
    ) as balance_increase
FROM 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
    ) as balance_increase
FROM account_balance
ORDER BY 1,2;

Recommendations

• If you need more support, you can email us at [email protected]