Snowflake函数内转换结果报错，求单函数实现优化方案

我想改造Snowflake中的DEVICE_PARSER函数，让输出结果更易读，但初始代码编译报错。我尝试拆分函数实现转换逻辑，不过更希望把所有逻辑整合到单个函数里。以下是我的初始代码、拆分尝试的代码，以及对应GCA业务线的示例数据（预期输出Mobile、Desktop、Tablet等结果）：

初始代码（编译报错） #

CREATE OR REPLACE FUNCTION example.example.DEVICE_PARSER("LOB" VARCHAR(16777216), "PLATFORM" VARCHAR(16777216), "PLACEMENT" VARCHAR(16777216))
RETURNS VARCHAR(16777216)
LANGUAGE SQL
AS '
    WITH cte AS (
        SELECT
            CASE
                WHEN UPPER("LOB") = ''GCA'' THEN
                    CASE 
                        WHEN UPPER("PLATFORM") = ''LINKEDIN'' THEN SPLIT_PART("PLACEMENT", ''_'', 4)
                        WHEN UPPER("PLATFORM") = ''TRADEDESK'' THEN SPLIT_PART("PLACEMENT", ''_'', 4)
                        WHEN UPPER("PLATFORM") = ''TWITTER'' THEN SPLIT_PART("PLACEMENT", ''_'', 4)
                        WHEN UPPER("PLATFORM") = ''META'' THEN SPLIT_PART("PLACEMENT", ''_'', 4)
                        WHEN UPPER("PLATFORM") = ''QUANTCAST'' THEN SPLIT_PART("PLACEMENT", ''_'', 4)
                        ELSE ''UNCLASSIFIED''
                    END
                ELSE ''UNCLASSIFIED''
            END AS device
        FROM (SELECT :LOB AS "LOB", :PLATFORM AS "PLATFORM", :PLACEMENT AS "PLACEMENT")
    )
    SELECT
        CASE
            WHEN UPPER(device) = ''MOB'' THEN ''Mobile''
            WHEN UPPER(device) = ''DESKTOP'' THEN ''Desktop''
            WHEN UPPER(device) = ''TABLET'' THEN ''Tablet''
            ELSE INITCAP(device)
        END AS result
    FROM cte;
';

拆分尝试的代码 #

-- 创建单独的转换函数
CREATE OR REPLACE FUNCTION transform_device(device VARCHAR(16777216))
RETURNS VARCHAR(16777216)
LANGUAGE SQL
AS '
    CASE
        WHEN UPPER(device) = ''MOB'' THEN ''Mobile''
        WHEN UPPER(device) = ''DESK'' THEN ''Desktop''
        WHEN UPPER(device) = ''TAB'' THEN ''Tablet''
        ELSE INITCAP(device) -- 原代码拼写错误：INTCAP → INITCAP
    END;
';

-- 修改原函数调用转换函数（存在错误）
CREATE OR REPLACE FUNCTION example.example.DEVICE_PARSER("LOB" VARCHAR(16777216), "PLATFORM" VARCHAR(16777216), "PLACEMENT" VARCHAR(16777216))
RETURNS VARCHAR(16777216)
LANGUAGE SQL
AS '
    CASE
        WHEN UPPER("LOB") = ''GCA'' THEN
            CASE 
                WHEN UPPER("PLATFORM") = ''LINKEDIN'' THEN SPLIT_PART("PLACEMENT", ''_'', 4)
                WHEN UPPER("PLATFORM") = ''TRADEDESK'' THEN SPLIT_PART("PLACEMENT", ''_'', 4)
                WHEN UPPER("PLATFORM") = ''TWITTER'' THEN SPLIT_PART("PLACEMENT", ''_'', 4)
                WHEN UPPER("PLATFORM") = ''META'' THEN SPLIT_PART("PLACEMENT", ''_'', 4)
                WHEN UPPER("PLATFORM") = ''QUANTCAST'' THEN SPLIT_PART("PLACEMENT", ''_'', 4)
                ELSE ''UNCLASSIFIED''
            END
        ELSE ''UNCLASSIFIED''
    END AS device,
    transform_device(device) AS result
';

示例数据 #

正确的单个函数实现 #

初始代码报错原因是Snowflake SQL函数不能通过CTE+SELECT的方式返回结果，需直接用表达式返回单个值；拆分后的第二个函数错误在于试图返回多个列，且存在拼写错误。以下是整合所有逻辑的单个函数：

CREATE OR REPLACE FUNCTION example.example.DEVICE_PARSER("LOB" VARCHAR(16777216), "PLATFORM" VARCHAR(16777216), "PLACEMENT" VARCHAR(16777216))
RETURNS VARCHAR(16777216)
LANGUAGE SQL
AS '
    CASE
        WHEN UPPER("LOB") = ''GCA'' THEN
            CASE
                WHEN UPPER("PLATFORM") IN (''LINKEDIN'', ''TRADEDESK'', ''TWITTER'', ''META'', ''QUANTCAST'') THEN
                    CASE UPPER(SPLIT_PART("PLACEMENT", ''_'', 4))
                        WHEN ''MOB'' THEN ''Mobile''
                        WHEN ''DESK'' THEN ''Desktop'' -- 匹配示例中的"Des"
                        WHEN ''DESKTOP'' THEN ''Desktop''
                        WHEN ''TAB'' THEN ''Tablet'' -- 匹配示例中的"TAB"
                        WHEN ''TABLET'' THEN ''Tablet''
                        ELSE INITCAP(SPLIT_PART("PLACEMENT", ''_'', 4))
                    END
                ELSE ''UNCLASSIFIED''
            END
        ELSE ''UNCLASSIFIED''
    END
';

函数说明 #

1. 先判断业务线是否为GCA，再判断平台是否在目标列表中
2. 提取Placement的第4段后，通过嵌套CASE转换为易读的设备名称
3. 处理了示例中的缩写（MOB→Mobile、DES/TAB→Desktop/Tablet），同时兼容完整词（DESKTOP/TABLET）
4. 未匹配到的情况使用INITCAP格式化，或返回UNCLASSIFIED

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