Snowflake MERGE查询类型不匹配问题求助（VARIANT/VARCHAR）

我明白你碰到的棘手问题了：你的W_TESTTABLE表中ATTRIBUTES列是VARIANT类型，但直接用MERGE传入字符串会触发类型不匹配的编译错误；当你尝试在VALUES子句里用PARSE_JSON转换时，又遇到了"VALUES子句中表达式无效"的报错，可单独用INSERT结合PARSE_JSON却能正常运行。

问题根源在于Snowflake的VALUES子句限制——它不支持直接在里面使用PARSE_JSON这类转换函数。我们需要把解析逻辑移到外层的SELECT语句中，先把字符串转换成VARIANT类型，再作为源数据供MERGE使用。

下面是修正后的完整MERGE语句：

MERGE INTO "W_TESTTABLE" T 
USING (
    SELECT 
        PARSE_JSON(ATTR_STR) AS "ATTRIBUTES",
        "ID",
        "OWNERID",
        "ISDELETED",
        "NAME",
        "CREATEDDATE",
        "CREATEDBYID",
        "LASTMODIFIEDDATE",
        "LASTMODIFIEDBYID",
        "SYSTEMMODSTAMP",
        "LASTACTIVITYDATE",
        "LASTVIEWEDDATE",
        "LASTREFERENCEDDATE",
        "ASSIGNED_BY__C",
        "FIELD11111111111111111111111111111111111__C",
        "RFIELD1111111111111111111111111111111111__C",
        "ISSUE_ID__C",
        "ORDERSTEST__C"
    FROM (
        VALUES 
        (
            '{"type":"issue__c", "url":"/services/data/v42.0"}',
            'a0E2v00001jiirTEAQ',
            '00590000005myFsAAI',
            'false',
            'test account',
            '2019-12-18T14:15:06.000000+05:30',
            '00590000005myFsAAI',
            '2019-12-18T14:15:06.000000+05:30',
            '00590000005myFsAAI',
            '2019-12-18T14:15:06.000000+05:30',
            NULL,
            '2019-12-18T14:15:07.000000+05:30',
            '2019-12-18T14:15:07.000000+05:30',
            NULL,
            NULL,
            NULL,
            NULL,
            NULL
        ) AS UNUSED_ALIAS (
            ATTR_STR,
            "ID",
            "OWNERID",
            "ISDELETED",
            "NAME",
            "CREATEDDATE",
            "CREATEDBYID",
            "LASTMODIFIEDDATE",
            "LASTMODIFIEDBYID",
            "SYSTEMMODSTAMP",
            "LASTACTIVITYDATE",
            "LASTVIEWEDDATE",
            "LASTREFERENCEDDATE",
            "ASSIGNED_BY__C",
            "FIELD11111111111111111111111111111111111__C",
            "RFIELD1111111111111111111111111111111111__C",
            "ISSUE_ID__C",
            "ORDERSTEST__C"
        )
    )
) S 
ON (T."ID" = S."ID") 
WHEN MATCHED THEN 
    UPDATE SET 
        T."ATTRIBUTES"=S."ATTRIBUTES",
        T."OWNERID"=S."OWNERID",
        T."ISDELETED"=S."ISDELETED",
        T."NAME"=S."NAME",
        T."CREATEDDATE"=S."CREATEDDATE",
        T."CREATEDBYID"=S."CREATEDBYID",
        T."LASTMODIFIEDDATE"=S."LASTMODIFIEDDATE",
        T."LASTMODIFIEDBYID"=S."LASTMODIFIEDBYID",
        T."SYSTEMMODSTAMP"=S."SYSTEMMODSTAMP",
        T."LASTACTIVITYDATE"=S."LASTACTIVITYDATE",
        T."LASTVIEWEDDATE"=S."LASTVIEWEDDATE",
        T."LASTREFERENCEDDATE"=S."LASTREFERENCEDDATE",
        T."ASSIGNED_BY__C"=S."ASSIGNED_BY__C",
        T."FIELD11111111111111111111111111111111111__C"=S."FIELD11111111111111111111111111111111111__C",
        T."RFIELD1111111111111111111111111111111111__C"=S."RFIELD1111111111111111111111111111111111__C",
        T."ISSUE_ID__C"=S."ISSUE_ID__C",
        T."ORDERSTEST__C"=S."ORDERSTEST__C" 
WHEN NOT MATCHED THEN 
    INSERT (
        "ATTRIBUTES",
        "ID",
        "OWNERID",
        "ISDELETED",
        "NAME",
        "CREATEDDATE",
        "CREATEDBYID",
        "LASTMODIFIEDDATE",
        "LASTMODIFIEDBYID",
        "SYSTEMMODSTAMP",
        "LASTACTIVITYDATE",
        "LASTVIEWEDDATE",
        "LASTREFERENCEDDATE",
        "ASSIGNED_BY__C",
        "FIELD11111111111111111111111111111111111__C",
        "RFIELD1111111111111111111111111111111111__C",
        "ISSUE_ID__C",
        "ORDERSTEST__C"
    ) 
    VALUES (
        S."ATTRIBUTES",
        S."ID",
        S."OWNERID",
        S."ISDELETED",
        S."NAME",
        S."CREATEDDATE",
        S."CREATEDBYID",
        S."LASTMODIFIEDDATE",
        S."LASTMODIFIEDBYID",
        S."SYSTEMMODSTAMP",
        S."LASTACTIVITYDATE",
        S."LASTVIEWEDDATE",
        S."LASTREFERENCEDDATE",
        S."ASSIGNED_BY__C",
        S."FIELD11111111111111111111111111111111111__C",
        S."RFIELD1111111111111111111111111111111111__C",
        S."ISSUE_ID__C",
        S."ORDERSTEST__C"
    )

关键修改点说明： #

• 把原来VALUES子句中的JSON字符串列命名为ATTR_STR（临时名称）
• 在外层SELECT中，使用PARSE_JSON(ATTR_STR)将字符串解析为VARIANT类型，并映射为目标表的ATTRIBUTES列
• 这样源表S中的ATTRIBUTES列类型就和目标表T的列类型完全匹配，同时避开了VALUES子句不支持转换函数的限制

这个思路和你成功的INSERT语句逻辑一致——都是先完成类型转换，再把数据传入目标表，只不过在MERGE中需要把转换逻辑放在USING子查询的外层SELECT里。

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