Snowflake中CASE WHEN内嵌子查询取值报错，求解决方案

一、先修正临时表t1的插入逻辑 #

你当前的t1插入语句会把sl_table的所有行都导入t1，每个id的每一行都会重复带上同一个max_filingdate，这大概率不是你需要的。如果目标是每个id只保留filingdate最大的那条记录，应该改成下面的写法：

INSERT INTO t1
SELECT max_filingdate, id, value
FROM (
    SELECT 
        MAX(filingdate) OVER (PARTITION BY id) AS max_filingdate,
        id,
        value,
        filingdate,
        -- 给每个id的行按filingdate降序排名，取第一行就是最大日期的记录
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY filingdate DESC) AS rn
    FROM sl_table
) t
WHERE rn = 1;

这样t1里每个id只会有一行数据，对应该id最新的filingdate和对应的value，后续关联时不会出现多行匹配的问题。

二、修正t2的插入语句，避免子查询报错 #

你原来的CASE语句里的子查询存在两个问题：一是引用了正在插入的目标表t2（多数SQL引擎不允许这种操作），二是子查询没有限制条件，会返回多行导致报错。下面提供两种可行方案：

方案1：直接在t2的查询中计算窗口函数，无需临时表t1 #

如果你的需求只是获取每个id的MAX(filingdate)，完全可以不用t1，直接在插入t2的语句里用窗口函数计算：

INSERT INTO t2
SELECT 
    id,
    asOfDate,
    filingdate,
    value,
    CASE
        -- 注意：MAX(AsOfDate)需明确范围，若要每个id的最大AsOfDate，也要用窗口函数
        WHEN MAX(asOfDate) OVER (PARTITION BY id) <= filingdate THEN 
            -- 直接取当前id对应最大filingdate的value
            FIRST_VALUE(value) OVER (PARTITION BY id ORDER BY filingdate DESC)
        ELSE value
    END AS t2_value
FROM sl_table
-- 你的关联和筛选语句放在这里
GROUP BY id, asofdate, filingdate, value;

方案2：关联修正后的t1来获取对应value #

如果你一定要用临时表t1，就把t1和原表sl_table关联，而非和目标表t2关联：

INSERT INTO t2
SELECT 
    s.id,
    s.asOfDate,
    s.filingdate,
    s.value,
    CASE
        -- 根据实际需求调整MAX(AsOfDate)的范围
        WHEN MAX(s.asOfDate) OVER (PARTITION BY s.id) <= s.filingdate THEN t1.value
        ELSE s.value
    END AS t2_value
FROM sl_table s
-- 按id关联t1
LEFT JOIN t1 ON s.id = t1.id
-- 你的关联和筛选语句放在这里
GROUP BY s.id, s.asofdate, s.filingdate, s.value, t1.value;

关键问题说明 #

1. 你之前的报错“Unsupported subquery type cannot be evaluated”，核心原因是CASE语句里的子查询返回多行，且引用了正在写入的目标表t2，多数SQL引擎不支持这种写法。
2. 用CTE时出现“子查询返回行数过多”，本质是因为原来的t1里每个id存在重复数据，修正t1的插入逻辑后即可解决。

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