Oracle Window function not working in SnowFlake

1
votes

i am working on Oracle to Snowflake migration.

while migrating oracle window functions to snowflake getting below error, could you let me know, alternate way for oracle function in snowflake.

SELECT 
      COL1,
      COL2, ...,
      SUM(SUM(TAB1.COL1)) OVER (PARTITION BY 
          TAB1.COL2,
          TAB1.COL3,
          TAB1.COL4,
          TAB1.COL5,
          TAB1.COL6,
          TAB1.COL7,
          TAB1.COL8,
          TAB1.COL9,
          TAB1.COL10,
      ORDER BY MAX(CALENDAR_TAB.DATE_COLUMN) RANGE BETWEEN INTERVAL '21' DAY PRECEDING  AND CURRENT ROW)/4 AS COLMN

      FROM TAB1,CALENDAR_TAB
      JOIN
      GROUP BYCOL1,
      COL2, ...

Below is the error message: QL Error [1003] [42000]: SQL compilation error: syntax error line 75 at position 60 unexpected 'INTERVAL'. syntax error line 75 at position 78 unexpected 'PRECEDING'.

2
database-migrationsnowflake-cloud-data-platform

2 Answers

2
votes

Per the documentation for Snowflake, here is the syntax:

https://docs.snowflake.com/en/sql-reference/functions-analytic.html#window-syntax-and-usage

slidingFrame ::=
    {
       ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND <N> { PRECEDING | FOLLOWING }
     | ROWS BETWEEN UNBOUNDED PRECEDING AND <N> { PRECEDING | FOLLOWING }
     | ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
    }

It might not like the INTERVAL and the quoted number.

2
votes

The Window frame document is a good place to start.

If I read the Oracle syntax correctly, the window frame your are using for the MAX is value based aka (interval '21' day) which Snowflake does not support, it only supports N rows based logic. If you have 1 row per day, and always 1 row, then you can use the row count logic, but otherwise this is not supported.

Which means you to join back to your own data tables and apply the prior time filter on the join.