SSC-FDM-OR0049

LAG function might fail if default value type differs from the expression type.

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

In Oracle, the LAG function automatically converts the default value's data type to match the expression's type. Snowflake, however, does not perform this implicit conversion. Therefore, a warning is issued to indicate that the LAG function may fail if the data types are incompatible.

Example Code

Input Code:

IN -> Oracle_01.sql

SELECT 
    LAG(salary, 2, '0') OVER (ORDER BY salary) AS salary_two_steps_back
FROM 
    employees;

Output Code:

OUT -> Oracle_01.sql

SELECT
    --** SSC-FDM-OR0049 - LAG FUNCTION MIGHT FAIL IF DEFAULT VALUE TYPE DIFFERS FROM THE EXPRESSION TYPE. **
    LAG(salary, 2, '0')
    OVER (ORDER BY salary) AS salary_two_steps_back
FROM
    employees;

Recommendations

• Verify that the data type of the default value matches the data type of the expression in the LAG function. If they differ, explicitly cast the default value to the expression's data type.

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