Snowflake UDF关联子查询报错：不支持的子查询类型求解

解决方案1：将标量函数改为表值函数，通过CROSS APPLY关联调用 #

Snowflake标量函数在处理依赖外部查询列的关联逻辑时，容易触发子查询不支持的错误。改用表值函数配合CROSS APPLY可以绕过这个限制：

1. 重新创建表值函数：

CREATE OR REPLACE FUNCTION MyDb.Schema.ObjectValue(obj_id INT, code varchar(255), end_date date)
RETURNS TABLE (prop_val varchar(500))
AS
$$
   SELECT TOP 1 prop_val
   FROM table1 op
       JOIN table2 dd ON op.id = dd.id
       LEFT JOIN table3 tt ON tt.tran_id = op.tran_id
   WHERE op.object_id = obj_id
       AND dd.code = code
       AND tt.eff_date <= end_date 
   ORDER BY tt.eff_date DESC, tt.tran_id DESC
$$

2. 修改CTE查询，用CROSS APPLY调用函数：

With t AS (....),
      q as (
         SELECT 
               t.name AS my_name,
               t.object_id AS obj_id,
               ov.prop_val AS end_date
          FROM t
          CROSS APPLY MyDb.Schema.ObjectValue(t.object_id, 'MY_DATE', '2023-12-30') ov
     ) 
SELECT * FROM q

解决方案2：将函数逻辑内联到主查询，用窗口函数替代标量函数 #

直接把函数中的查询逻辑合并到CTE里，用ROW_NUMBER()窗口函数实现取TOP 1的需求，完全避免自定义函数的使用：

With t AS (....),
      joined_data AS (
         SELECT 
               t.name AS my_name,
               t.object_id AS obj_id,
               op.prop_val,
               ROW_NUMBER() OVER (PARTITION BY t.object_id ORDER BY tt.eff_date DESC, tt.tran_id DESC) AS rn
          FROM t
          JOIN table1 op ON op.object_id = t.object_id
          JOIN table2 dd ON op.id = dd.id
          LEFT JOIN table3 tt ON tt.tran_id = op.tran_id
          WHERE dd.code = 'MY_DATE'
              AND tt.eff_date <= '2023-12-30'
     )
SELECT my_name, obj_id, prop_val AS end_date
FROM joined_data
WHERE rn = 1

解决方案3：用LATERAL关联直接嵌入子查询 #

如果更习惯LATERAL语法，也可以直接在主查询中嵌入原函数的子查询逻辑：

With t AS (....),
      q as (
         SELECT 
               t.name AS my_name,
               t.object_id AS obj_id,
               ov.prop_val AS end_date
          FROM t,
               LATERAL (
                   SELECT TOP 1 prop_val
                   FROM table1 op
                       JOIN table2 dd ON op.id = dd.id
                       LEFT JOIN table3 tt ON tt.tran_id = op.tran_id
                   WHERE op.object_id = t.object_id
                       AND dd.code = 'MY_DATE'
                       AND tt.eff_date <= '2023-12-30' 
                   ORDER BY tt.eff_date DESC, tt.tran_id DESC
               ) ov
     ) 
SELECT * FROM q

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