SSC-FDM-OR0049

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

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 [e-mail]