Snowflake SQL UDF传列参数报错，求替代解决方法

Snowflake SQL UDF在传入硬编码参数时运行正常，但在SELECT子句中传入实际表列作为参数时，触发SQL compilation error: Unsupported subquery type cannot be evaluated错误，该问题多年前已被社区反馈但暂无官方解决方案。

原UDF定义 #

CREATE OR REPLACE FUNCTION UDF_GET_CURR_CONV_VALUES(BASE_NET_VALUE FLOAT,EX_PRICE_DATE DATE,EX_RATE_TYPE VARCHAR(20),FROM_CURR VARCHAR(10),TO_CURR VARCHAR(10))
RETURNS VARCHAR(16777216)
LANGUAGE SQL
COMMENT='This function will return Ex rate value, net value and converted net value based on the input parameter.'
AS $$
case 
    when FROM_CURR = TO_CURR then ('|'||BASE_NET_VALUE||'|'||BASE_NET_VALUE)
else 
    (select (EXCHANGE_RATE_VALUE||'|'||ACT_BASE_NET_VALUE||'|'||CONV_NET_VALUE) 
    from
        (select
            case 
                when ( (2 - CURRENCY.CURRENCY_DECIMAL_PLACES) < 0 ) then round((BASE_NET_VALUE / power(10, -1 * (2 - CURRENCY.CURRENCY_DECIMAL_PLACES)))/TCURF.FROM_CURRENCY_RATIO,2) 
                when ( (2 - CURRENCY.CURRENCY_DECIMAL_PLACES) > 0 ) then round((BASE_NET_VALUE * power(10, 1 * (2 - CURRENCY.CURRENCY_DECIMAL_PLACES)))/TCURF.FROM_CURRENCY_RATIO,2)
            else round(BASE_NET_VALUE,2) end as ACT_BASE_NET_VALUE
            ,round((ACT_BASE_NET_VALUE * EXRATE.EXCHANGE_RATE_VALUE),2) as CONV_NET_VALUE
            ,EXRATE.EXCHANGE_RATE_VALUE as EXCHANGE_RATE_VALUE
        from MY_SCHEMA.MY_EXCHANGE_RATES EXRATE
        LEFT JOIN MY_SCHEMA.CURRENCY CURRENCY ON CURRENCY.CURRENCY_KEY = FROM_CURR AND CURRENCY.DELETED = 'N'
        LEFT JOIN (select * from MY_SCHEMA.EXCHANGE_RATE_CONVERSION_FACTORS where DELETED = 'N'
                    QUALIFY ROW_NUMBER() OVER (PARTITION BY EXCHANGE_RATE_TYPE,FROM_CURRENCY,TO_CURRENCY,VALID_FROM ORDER BY VALID_FROM DESC) = 1) TCURF
                    ON TCURF.FROM_CURRENCY = FROM_CURR AND TCURF.TO_CURRENCY = TO_CURR AND TCURF.EXCHANGE_RATE_TYPE = EX_RATE_TYPE
        where equal_null(FROM_CURR,EXRATE.SOURCE_CURRENCY) 
            and EXRATE.EXCHANGE_RATE_TYPE = EX_RATE_TYPE
            and (EX_PRICE_DATE BETWEEN EXRATE.EXCHANGE_RATE_DATE AND EXRATE.VALID_TO_DATE)
            and EXRATE.TARGET_CURRENCY = TO_CURR
            and EXRATE.DELETED = 'N'
        ))
end
$$;

正常调用（硬编码参数） #

select 
     try_to_double(split_part(my_schema.UDF_GET_CURR_CONV_VALUES(44131.26,to_date('2020-04-24'),'M','EUR','USD'),'|',1)) as EX_RATE_VALUE 
    ,try_to_double(split_part(my_schema.UDF_GET_CURR_CONV_VALUES(44131.26,to_date('2020-04-24'),'M','EUR','USD'),'|',2)) as BASE_VALUE
    ,try_to_double(split_part(my_schema.UDF_GET_CURR_CONV_VALUES(44131.26,to_date('2020-04-24'),'M','EUR','USD'),'|',3)) as USD_BASE_VALUE
;

报错调用（传入表列参数） #

select
    TXN_NO
    ,try_to_double(split_part(my_schema.UDF_GET_CURR_CONV_VALUES(NET_VALUE,PRICE_DATE,RATE_TYPE,SOURCE_CURRENCY,TARGET_CURRENCY),'|',1)) as EX_RATE_VALUE 
    ,try_to_double(split_part(my_schema.UDF_GET_CURR_CONV_VALUES(NET_VALUE,PRICE_DATE,RATE_TYPE,SOURCE_CURRENCY,TARGET_CURRENCY),'|',2)) as BASE_VALUE
    ,try_to_double(split_part(my_schema.UDF_GET_CURR_CONV_VALUES(NET_VALUE,PRICE_DATE,RATE_TYPE,SOURCE_CURRENCY,TARGET_CURRENCY),'|',3)) as USD_BASE_VALUE
FROM MY_SCHEMA.MY_TRANSACTION_TABLE
WHERE TXN_NO = 'ABCXYZ'
;

可行替代方案 #

方案1：改用表值函数（Table UDF） #

将标量UDF改为返回多列的表值函数，避免嵌套子查询在标量UDF中的限制：

CREATE OR REPLACE FUNCTION TVF_GET_CURR_CONV_VALUES(BASE_NET_VALUE FLOAT,EX_PRICE_DATE DATE,EX_RATE_TYPE VARCHAR(20),FROM_CURR VARCHAR(10),TO_CURR VARCHAR(10))
RETURNS TABLE(EXCHANGE_RATE_VALUE FLOAT, ACT_BASE_NET_VALUE FLOAT, CONV_NET_VALUE FLOAT)
LANGUAGE SQL
COMMENT='Returns exchange rate, adjusted base value, and converted net value'
AS $$
SELECT
    CASE WHEN FROM_CURR = TO_CURR THEN NULL ELSE EXRATE.EXCHANGE_RATE_VALUE END AS EXCHANGE_RATE_VALUE,
    CASE 
        WHEN FROM_CURR = TO_CURR THEN BASE_NET_VALUE
        WHEN (2 - CURRENCY.CURRENCY_DECIMAL_PLACES) < 0 THEN ROUND((BASE_NET_VALUE / POWER(10, -1 * (2 - CURRENCY.CURRENCY_DECIMAL_PLACES)))/TCURF.FROM_CURRENCY_RATIO,2)
        WHEN (2 - CURRENCY.CURRENCY_DECIMAL_PLACES) > 0 THEN ROUND((BASE_NET_VALUE * POWER(10, 1 * (2 - CURRENCY.CURRENCY_DECIMAL_PLACES)))/TCURF.FROM_CURRENCY_RATIO,2)
        ELSE ROUND(BASE_NET_VALUE,2)
    END AS ACT_BASE_NET_VALUE,
    CASE WHEN FROM_CURR = TO_CURR THEN BASE_NET_VALUE ELSE ROUND(
        CASE 
            WHEN (2 - CURRENCY.CURRENCY_DECIMAL_PLACES) < 0 THEN ROUND((BASE_NET_VALUE / POWER(10, -1 * (2 - CURRENCY.CURRENCY_DECIMAL_PLACES)))/TCURF.FROM_CURRENCY_RATIO,2)
            WHEN (2 - CURRENCY.CURRENCY_DECIMAL_PLACES) > 0 THEN ROUND((BASE_NET_VALUE * POWER(10, 1 * (2 - CURRENCY.CURRENCY_DECIMAL_PLACES)))/TCURF.FROM_CURRENCY_RATIO,2)
            ELSE ROUND(BASE_NET_VALUE,2)
        END * EXRATE.EXCHANGE_RATE_VALUE,2)
    END AS CONV_NET_VALUE
FROM MY_SCHEMA.MY_EXCHANGE_RATES EXRATE
LEFT JOIN MY_SCHEMA.CURRENCY CURRENCY ON CURRENCY.CURRENCY_KEY = FROM_CURR AND CURRENCY.DELETED = 'N'
LEFT JOIN (
    SELECT * 
    FROM MY_SCHEMA.EXCHANGE_RATE_CONVERSION_FACTORS 
    WHERE DELETED = 'N'
    QUALIFY ROW_NUMBER() OVER (PARTITION BY EXCHANGE_RATE_TYPE,FROM_CURRENCY,TO_CURRENCY,VALID_FROM ORDER BY VALID_FROM DESC) = 1
) TCURF ON TCURF.FROM_CURRENCY = FROM_CURR AND TCURF.TO_CURRENCY = TO_CURR AND TCURF.EXCHANGE_RATE_TYPE = EX_RATE_TYPE
WHERE FROM_CURR != TO_CURR
    AND equal_null(FROM_CURR,EXRATE.SOURCE_CURRENCY)
    AND EXRATE.EXCHANGE_RATE_TYPE = EX_RATE_TYPE
    AND EX_PRICE_DATE BETWEEN EXRATE.EXCHANGE_RATE_DATE AND EXRATE.VALID_TO_DATE
    AND EXRATE.TARGET_CURRENCY = TO_CURR
    AND EXRATE.DELETED = 'N'
UNION ALL
SELECT NULL, BASE_NET_VALUE, BASE_NET_VALUE
WHERE FROM_CURR = TO_CURR
$$;

调用方式（使用LATERAL JOIN关联表值函数）：

SELECT
    T.TXN_NO,
    COALESCE(F.EXCHANGE_RATE_VALUE, 1) AS EX_RATE_VALUE,
    F.ACT_BASE_NET_VALUE AS BASE_VALUE,
    F.CONV_NET_VALUE AS USD_BASE_VALUE
FROM MY_SCHEMA.MY_TRANSACTION_TABLE T
LEFT JOIN LATERAL TVF_GET_CURR_CONV_VALUES(T.NET_VALUE, T.PRICE_DATE, T.RATE_TYPE, T.SOURCE_CURRENCY, T.TARGET_CURRENCY) F ON 1=1
WHERE T.TXN_NO = 'ABCXYZ';

方案2：将UDF逻辑内联到查询中，用JOIN替代 #

直接在主查询中关联汇率相关表，避免使用标量UDF：

WITH TCURF_PREP AS (
    SELECT * 
    FROM MY_SCHEMA.EXCHANGE_RATE_CONVERSION_FACTORS 
    WHERE DELETED = 'N'
    QUALIFY ROW_NUMBER() OVER (PARTITION BY EXCHANGE_RATE_TYPE,FROM_CURRENCY,TO_CURRENCY,VALID_FROM ORDER BY VALID_FROM DESC) = 1
)
SELECT
    T.TXN_NO,
    CASE WHEN T.SOURCE_CURRENCY = T.TARGET_CURRENCY THEN 1 ELSE EXRATE.EXCHANGE_RATE_VALUE END AS EX_RATE_VALUE,
    CASE 
        WHEN T.SOURCE_CURRENCY = T.TARGET_CURRENCY THEN T.NET_VALUE
        WHEN (2 - CURRENCY.CURRENCY_DECIMAL_PLACES) < 0 THEN ROUND((T.NET_VALUE / POWER(10, -1 * (2 - CURRENCY.CURRENCY_DECIMAL_PLACES)))/TCURF.FROM_CURRENCY_RATIO,2)
        WHEN (2 - CURRENCY.CURRENCY_DECIMAL_PLACES) > 0 THEN ROUND((T.NET_VALUE * POWER(10, 1 * (2 - CURRENCY.CURRENCY_DECIMAL_PLACES)))/TCURF.FROM_CURRENCY_RATIO,2)
        ELSE ROUND(T.NET_VALUE,2)
    END AS BASE_VALUE,
    CASE 
        WHEN T.SOURCE_CURRENCY = T.TARGET_CURRENCY THEN T.NET_VALUE
        ELSE ROUND(
            CASE 
                WHEN (2 - CURRENCY.CURRENCY_DECIMAL_PLACES) < 0 THEN ROUND((T.NET_VALUE / POWER(10, -1 * (2 - CURRENCY.CURRENCY_DECIMAL_PLACES)))/TCURF.FROM_CURRENCY_RATIO,2)
                WHEN (2 - CURRENCY.CURRENCY_DECIMAL_PLACES) > 0 THEN ROUND((T.NET_VALUE * POWER(10, 1 * (2 - CURRENCY.CURRENCY_DECIMAL_PLACES)))/TCURF.FROM_CURRENCY_RATIO,2)
                ELSE ROUND(T.NET_VALUE,2)
            END * EXRATE.EXCHANGE_RATE_VALUE,2)
    END AS USD_BASE_VALUE
FROM MY_SCHEMA.MY_TRANSACTION_TABLE T
LEFT JOIN MY_SCHEMA.MY_EXCHANGE_RATES EXRATE 
    ON T.SOURCE_CURRENCY != T.TARGET_CURRENCY
    AND equal_null(T.SOURCE_CURRENCY, EXRATE.SOURCE_CURRENCY)
    AND EXRATE.EXCHANGE_RATE_TYPE = T.RATE_TYPE
    AND T.PRICE_DATE BETWEEN EXRATE.EXCHANGE_RATE_DATE AND EXRATE.VALID_TO_DATE
    AND EXRATE.TARGET_CURRENCY = T.TARGET_CURRENCY
    AND EXRATE.DELETED = 'N'
LEFT JOIN MY_SCHEMA.CURRENCY CURRENCY 
    ON T.SOURCE_CURRENCY != T.TARGET_CURRENCY
    AND CURRENCY.CURRENCY_KEY = T.SOURCE_CURRENCY 
    AND CURRENCY.DELETED = 'N'
LEFT JOIN TCURF_PREP TCURF
    ON T.SOURCE_CURRENCY != T.TARGET_CURRENCY
    AND TCURF.FROM_CURRENCY = T.SOURCE_CURRENCY 
    AND TCURF.TO_CURRENCY = T.TARGET_CURRENCY 
    AND TCURF.EXCHANGE_RATE_TYPE = T.RATE_TYPE
WHERE T.TXN_NO = 'ABCXYZ';

方案说明 #

• 表值函数方案保留了逻辑的封装性，同时避免了标量UDF中嵌套子查询的限制，通过LATERAL JOIN实现逐行调用。
• 内联查询方案直接将UDF逻辑展开到主查询中，性能可能更优，适合不需要重复复用逻辑的场景。

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