SQL in Snowflake PRIOR keyword is missing in Connect By statement-CodePudding

I cannot get this code to execute in Snowflake SQL. The error message is PRIOR keyword is missing in Connect By statement. (line 5)

Any ideas?

select associate_id, position_effective_date, home_department_code, 
most_recent_record, (last_day(date_from_parts(year(current_date()), 
month(current_date())-Q_LEVEL,1),month)) AS month
from(
WITH Q AS (SELECT LEVEL Q_LEVEL FROM DUAL A CONNECT BY LEVEL <= 36)
select Q.Q_LEVEL Q_LEVEL, v_dept_history_adj.associate_id,             
v_dept_history_adj.home_department_code, 
v_dept_history_adj.position_effective_date, max(position_effective_date) 
OVER(PARTITION BY v_dept_history_adj.associate_id) AS most_recent_record 
from src_table, Q
where v_dept_history_adj.position_effective_date <= 
last_day(date_from_parts(year(current_date()), 
month(current_date())-Q.Q_LEVEL,1),month))
where position_effective_date = most_recent_record
order by month desc, position_effective_date desc

CodePudding user response：

The PRIOR keyword is required exactly once per CONNECT BY.

https://docs.snowflake.com/en/sql-reference/constructs/connect-by.html#usage-notes

The keyword PRIOR should occur exactly once in each CONNECT BY expression. PRIOR can occur on either the left-hand side or the right-hand side of the expression, but not on both.

The tell-tell "from dual" makes it appear that the SQL is from Oracle. There's another note higher up in that page:

The Snowflake syntax for CONNECT BY is mostly compatible with the Oracle syntax.

There are some differences, and the retirement to have PRIOR once on either the right or left of the expression is one of the differences.

Most likely the PRIOR keyword should come before LEVEL:

WITH Q AS (SELECT LEVEL Q_LEVEL FROM DUAL A CONNECT BY prior LEVEL <= 36)

That will pass the syntax check on compile and is probably what you need here, but you can experiment with the position of the PRIOR keyword to be sure.

CodePudding user response：

WITH has to be the first in a query, like the manual described https://docs.snowflake.com/en/user-guide/queries-cte.html

So your query would look like

I can't test it right know, but src_table, Q looks very suspicious, besides using JOINs

Further v_dept_history_adj is nowhere defined, so i am guessing that it is the source_table

WITH Q AS (SELECT LEVEL Q_LEVEL FROM DUAL A CONNECT BY LEVEL <= 36),
Q1 AS (
select 
    Q.Q_LEVEL Q_LEVEL
    , v_dept_history_adj.associate_id,             
    v_dept_history_adj.home_department_code, 
    v_dept_history_adj.position_effective_date
    , max(position_effective_date) 
            OVER(PARTITION BY v_dept_history_adj.associate_id) AS most_recent_record 
from src_table, Q
where v_dept_history_adj.position_effective_date <= 
last_day(date_from_parts(year(current_date()), 
month(current_date())-Q.Q_LEVEL,1),month))
select 
    associate_id
    , position_effective_date
    , home_department_code, 
    most_recent_record
      , (last_day(date_from_parts(year(current_date())
    ,month(current_date())-Q_LEVEL,1),month)) AS month
    FRO Q1
where position_effective_date = most_recent_record
order by month desc, position_effective_date desc