Snowflake SQL执行报错：Connect By语句缺失PRIOR关键字

• 报错原因：Snowflake的CONNECT BY语法强制要求包含PRIOR关键字来定义递归关联关系，原代码中采用Oracle风格的SELECT LEVEL Q_LEVEL FROM DUAL A CONNECT BY LEVEL <= 36生成序列的写法，不符合Snowflake的语法规范，因此触发该错误。

• 解决方案：替换生成1到36连续序列的方式，Snowflake提供两种常用实现方式：

方式1：使用GENERATOR函数 #

GENERATOR(ROWCOUNT => 36)可直接生成指定行数的空表，配合ROW_NUMBER()函数生成1到36的序列值：

WITH Q AS (
    SELECT ROW_NUMBER() OVER(ORDER BY NULL) AS Q_LEVEL 
    FROM TABLE(GENERATOR(ROWCOUNT => 36))
)

方式2：使用递归CTE #

通过递归查询逐步生成序列：

WITH Q AS (
    SELECT 1 AS Q_LEVEL
    UNION ALL
    SELECT Q_LEVEL + 1 FROM Q WHERE Q_LEVEL <= 35
)

完整修改后的SQL代码（以GENERATOR函数为例） #

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 ROW_NUMBER() OVER(ORDER BY NULL) AS Q_LEVEL 
        FROM TABLE(GENERATOR(ROWCOUNT => 36))
    )
    SELECT Q.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 v_dept_history_adj, 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

注：原代码中src_table应为笔误，实际关联表是v_dept_history_adj，已做修正。

内容的提问来源于stack exchange，提问作者Gavin