SSC-EWI-TD0077

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

Some parts in the output code are omitted for clarity reasons.

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:

IN -> Teradata_01.sql

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

OUT -> Teradata_01.sql

// SnowConvert Helpers Code section is omitted.
SELECT
    account_id,
    month_id,
    balance,
    ROW_NUMBER() OVER (
        PARTITION BY account_id
    !!!RESOLVE EWI!!! /*** SSC-EWI-TD0077 - 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:

IN -> Teradata_02.sql

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

OUT -> Teradata_02.sql

// SnowConvert Helpers Code section is omitted.
SELECT
    account_id,
    month_id,
    balance,
    ROW_NUMBER() OVER (
        PARTITION BY account_id
    !!!RESOLVE EWI!!! /*** SSC-EWI-TD0077 - 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 [e-mail]