无法计算雪花不支持的子查询

Question

/Table临时程序有客户散列、有效开始日期和有效结束日期。表CDTLS有客户散列，生效开始日期，我想要客户散列，生效从，客户名称从临时和CDTLS。我正在动态计算CDTLS结束日期，并将其与TEMP.EFFECTIVE_FROM和TEMP_EFFECTIVE_TO日期进行比较。我得到一个错误，无法计算不支持的子查询。

SELECT
        TEMP.CUSTOMER_HASH, 
        TEMP.EFFECTIVE_FROM,
        TEMP.EFFECTIVE_TO,
        CDTLS.NAME
    FROM TEMP
    LEFT CDTLS 
    ON 
    TEMP.CUSTOMER_HASH = CDTLS.CUSTOMER_HASH
    AND 
    CDTLS.EFFECTIVE_FROM <= TEMP.EFFECTIVE_FROM
    AND 
     (
     SELECT VW.EFFECTIVE_TO FROM  
     
        (
         SELECT CUSTOMER_HASH, EFFECTIVE_FROM, LEAD(EFFECTIVE_FROM, 1, '9999-12-31') OVER (PARTITION 
              BY CUSTOMER_HASH ORDER BY EFFECTIVE_FROM ASC) AS EFFECTIVE_TO
         FROM CUST_DETAILS
        ) AS VW
     WHERE CDTLS.CUSTOMER_HASH = VW.CUSTOMER_HASH AND CDTLS.EFFECTIVE_FROM = VW.EFFECTIVE_FROM
     ) >= TEMP.EFFECTIVE_TO
    ;

Answer 1

我想您想要运行这个查询：

​

SELECT
        TEMP.CUSTOMER_HASH, 
        TEMP.EFFECTIVE_FROM,
        TEMP.EFFECTIVE_TO,
        CDTLS.NAME
    FROM TEMP
    LEFT join  CDTLS 
    ON 
    TEMP.CUSTOMER_HASH = CDTLS.CUSTOMER_HASH
    AND 
    CDTLS.EFFECTIVE_FROM <= TEMP.EFFECTIVE_FROM
    left join         (
         SELECT CUSTOMER_HASH, EFFECTIVE_FROM, LEAD(EFFECTIVE_FROM, 1, '9999-12-31') OVER (PARTITION 
              BY CUSTOMER_HASH ORDER BY EFFECTIVE_FROM ASC) AS EFFECTIVE_TO
         FROM CUST_DETAILS
        ) AS VW on CDTLS.CUSTOMER_HASH = VW.CUSTOMER_HASH AND CDTLS.EFFECTIVE_FROM = VW.EFFECTIVE_FROM
    where  
   VW.EFFECTIVE_TO >= TEMP.EFFECTIVE_TO

​

Answer 2

您可以尝试在select查询中使用MIN / MAX / LISTAGG等来确定标量，以检查这是否有帮助。

https://docs.snowflake.net/manuals/user-guide/querying-subqueries.html#differences-between-correlated-and-non-correlated-subqueries