Snowflake中使用CTE WITH子句执行INSERT语句时出现语法错误

问题描述 #

在Snowflake中使用CTE（WITH子句）定义temp_product和latest_custodian两个临时结果集后，尝试向目标表ent.P_ACCOUNTS_EXTRACT_SHR插入数据时，抛出编译错误：

syntax error line 27 at position 4 unexpected 'INSERT'

原SQL代码如下：

with temp_Product 
    AS (
        select Product_EDW_Id From ent.PRODUCT
    Where 
      PRODUCT_CLASS_NM IN ('Individual', 'Composite')
    UNION ALL
    SELECT R.Participating_Product_EDW_Id FROM ent.PRODUCT p
    INNER JOIN ent.Product_Group_Relationship R
    ON P.PRODUCT_EDW_ID = R.PRODUCT_EDW_ID
    ),
 Latest_Custodian
    AS (
    SELECT 
        c.Src_Sys_Custodian_Account_Id, 
        c.client_edw_id, 
        c.Create_dt,
        ROW_NUMBER() OVER(PARTITION BY c.Src_Sys_Custodian_Account_Id, c.client_edw_id 
                          ORDER BY c.Create_dt DESC) as RNUM1
    FROM ent.Product P
        INNER JOIN temp_Product TP
        ON P.Product_EDW_Id = TP.Product_EDW_Id
    Left Join ent.custodian C
    ON P.UDF7_TX = C.SRC_SYS_CUSTODIAN_ACCOUNT_ID AND 
        P.CLIENT_EDW_ID = C.CLIENT_EDW_ID
    )
    
    INSERT INTO ent.P_ACCOUNTS_EXTRACT_SHR (
        IS_FLAG,
        CREATE_DT,
        ACCOUNT_CODE,
        TRADABLE_DATE,
        CLOSE_DATE,
        ACCOUNT_TYPE,
        TRUST_OFFICER,
        TRUST_OFFICER_CITY,
        TRUST_OFFICER_PHONE,
        STATEMENT_ACCOUNT_NUMBER,
        CUSTODIAN_CODE,
        CUSTODIAN,
        TAXABLE,
        ACCOUNT_ATTRIBUTE,
        ACCOUNT_NAME 
    )
    SELECT 
        '0',
        CURRENT_TIMESTAMP(),
        P.Client_Product_Id,
        P.INCEPTION_DT,
        P.src_sys_close_dt,
        P.UDF31_TX,
        P.UDF15_TX,
        P.UDF16_TX,
        P.UDF17_TX,
        P.UDF7_TX,
        C1.custodian_mnemonic_nm,
        P.UDF8_TX,
        P.tax_exempt_fl,
        P.udf35_tx,
        P.PRODUCT_NM
    from ent.PRODUCT P
    INNER JOIN temp_Product TP
    ON P.Product_EDW_Id = TP.Product_EDW_Id
    LEFT JOIN Latest_Custodian C
    ON P.UDF7_TX = C.SRC_SYS_CUSTODIAN_ACCOUNT_ID AND
        P.client_edw_id = C.client_edw_id AND
        RNUM1 = 1
    LEFT JOIN ent.CUSTODIAN C1
    ON C.SRC_SYS_CUSTODIAN_ACCOUNT_ID = C1.src_sys_custodian_account_id AND
    C.create_dt = C1.CREATE_DT
    ORDER BY P.product_structure_level_nm;

错误原因 #

Snowflake的语法规范要求：WITH子句必须直接衔接在要使用它的INSERT/SELECT/UPDATE/MERGE语句之前，不能让WITH成为独立的语法块后再执行目标语句。原代码中WITH定义结束后，没有直接紧跟INSERT，导致解析器无法识别INSERT与CTE的关联关系。

修正后的SQL #

以下两种写法均符合Snowflake语法规范：

写法一：WITH子句前置 #

WITH temp_Product 
    AS (
        select Product_EDW_Id From ent.PRODUCT
    Where 
      PRODUCT_CLASS_NM IN ('Individual', 'Composite')
    UNION ALL
    SELECT R.Participating_Product_EDW_Id FROM ent.PRODUCT p
    INNER JOIN ent.Product_Group_Relationship R
    ON P.PRODUCT_EDW_ID = R.PRODUCT_EDW_ID
    ),
 Latest_Custodian
    AS (
    SELECT 
        c.Src_Sys_Custodian_Account_Id, 
        c.client_edw_id, 
        c.Create_dt,
        ROW_NUMBER() OVER(PARTITION BY c.Src_Sys_Custodian_Account_Id, c.client_edw_id 
                          ORDER BY c.Create_dt DESC) as RNUM1
    FROM ent.Product P
        INNER JOIN temp_Product TP
        ON P.Product_EDW_Id = TP.Product_EDW_Id
    Left Join ent.custodian C
    ON P.UDF7_TX = C.SRC_SYS_CUSTODIAN_ACCOUNT_ID AND 
        P.CLIENT_EDW_ID = C.CLIENT_EDW_ID
    )
INSERT INTO ent.P_ACCOUNTS_EXTRACT_SHR (
    IS_FLAG,
    CREATE_DT,
    ACCOUNT_CODE,
    TRADABLE_DATE,
    CLOSE_DATE,
    ACCOUNT_TYPE,
    TRUST_OFFICER,
    TRUST_OFFICER_CITY,
    TRUST_OFFICER_PHONE,
    STATEMENT_ACCOUNT_NUMBER,
    CUSTODIAN_CODE,
    CUSTODIAN,
    TAXABLE,
    ACCOUNT_ATTRIBUTE,
    ACCOUNT_NAME 
)
SELECT 
    '0',
    CURRENT_TIMESTAMP(),
    P.Client_Product_Id,
    P.INCEPTION_DT,
    P.src_sys_close_dt,
    P.UDF31_TX,
    P.UDF15_TX,
    P.UDF16_TX,
    P.UDF17_TX,
    P.UDF7_TX,
    C1.custodian_mnemonic_nm,
    P.UDF8_TX,
    P.tax_exempt_fl,
    P.udf35_tx,
    P.PRODUCT_NM
from ent.PRODUCT P
INNER JOIN temp_Product TP
ON P.Product_EDW_Id = TP.Product_EDW_Id
LEFT JOIN Latest_Custodian C
ON P.UDF7_TX = C.SRC_SYS_CUSTODIAN_ACCOUNT_ID AND
    P.client_edw_id = C.client_edw_id AND
    RNUM1 = 1
LEFT JOIN ent.CUSTODIAN C1
ON C.SRC_SYS_CUSTODIAN_ACCOUNT_ID = C1.src_sys_custodian_account_id AND
C.create_dt = C1.CREATE_DT
ORDER BY P.product_structure_level_nm;

写法二：INSERT前置，WITH紧跟其后 #

INSERT INTO ent.P_ACCOUNTS_EXTRACT_SHR (
    IS_FLAG,
    CREATE_DT,
    ACCOUNT_CODE,
    TRADABLE_DATE,
    CLOSE_DATE,
    ACCOUNT_TYPE,
    TRUST_OFFICER,
    TRUST_OFFICER_CITY,
    TRUST_OFFICER_PHONE,
    STATEMENT_ACCOUNT_NUMBER,
    CUSTODIAN_CODE,
    CUSTODIAN,
    TAXABLE,
    ACCOUNT_ATTRIBUTE,
    ACCOUNT_NAME 
)
WITH temp_Product 
    AS (
        select Product_EDW_Id From ent.PRODUCT
    Where 
      PRODUCT_CLASS_NM IN ('Individual', 'Composite')
    UNION ALL
    SELECT R.Participating_Product_EDW_Id FROM ent.PRODUCT p
    INNER JOIN ent.Product_Group_Relationship R
    ON P.PRODUCT_EDW_ID = R.PRODUCT_EDW_ID
    ),
 Latest_Custodian
    AS (
    SELECT 
        c.Src_Sys_Custodian_Account_Id, 
        c.client_edw_id, 
        c.Create_dt,
        ROW_NUMBER() OVER(PARTITION BY c.Src_Sys_Custodian_Account_Id, c.client_edw_id 
                          ORDER BY c.Create_dt DESC) as RNUM1
    FROM ent.Product P
        INNER JOIN temp_Product TP
        ON P.Product_EDW_Id = TP.Product_EDW_Id
    Left Join ent.custodian C
    ON P.UDF7_TX = C.SRC_SYS_CUSTODIAN_ACCOUNT_ID AND 
        P.CLIENT_EDW_ID = C.CLIENT_EDW_ID
    )
SELECT 
    '0',
    CURRENT_TIMESTAMP(),
    P.Client_Product_Id,
    P.INCEPTION_DT,
    P.src_sys_close_dt,
    P.UDF31_TX,
    P.UDF15_TX,
    P.UDF16_TX,
    P.UDF17_TX,
    P.UDF7_TX,
    C1.custodian_mnemonic_nm,
    P.UDF8_TX,
    P.tax_exempt_fl,
    P.udf35_tx,
    P.PRODUCT_NM
from ent.PRODUCT P
INNER JOIN temp_Product TP
ON P.Product_EDW_Id = TP.Product_EDW_Id
LEFT JOIN Latest_Custodian C
ON P.UDF7_TX = C.SRC_SYS_CUSTODIAN_ACCOUNT_ID AND
    P.client_edw_id = C.client_edw_id AND
    RNUM1 = 1
LEFT JOIN ent.CUSTODIAN C1
ON C.SRC_SYS_CUSTODIAN_ACCOUNT_ID = C1.src_sys_custodian_account_id AND
C.create_dt = C1.CREATE_DT
ORDER BY P.product_structure_level_nm;

说明 #

两种写法的核心都是保证WITH子句与INSERT语句直接衔接，让Snowflake解析器能正确识别CTE与插入操作的关联关系，从而避免语法错误。

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