雪花:合并查询:期望变体，但得到 VARCHAR(3038) 作为列

Question

SQL 编译错误：表达式类型与列数据类型不匹配，需要 VARIANT 但为列属性获取 VARCHAR(3038(。有人可以帮助更正查询吗？

MERGE INTO "W_TESTTABLE" T USING (SELECT * 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 ("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"))) 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")

我尝试为值做这样的PARSE_JSON

MERGE INTO "W_TESTTABLE" T USING (SELECT * FROM (VALUES (PARSE_JSON('{"type":"issue__c", "url":"/services/data/v42.0"}')....

但收到错误 值子句中的无效表达式 [PARSE_JSON('{"type"："issue__c"， "url"："/services/data/v42.0/sobjects/issue__c/a0E2v00001jiirTEAQ"}'(] ：

我能够成功执行插入查询

INSERT INTO  DEMO_DB.public.W_TEST(ID, ATTRIBUTES) SELECT 2, PARSE_JSON('{"first_name":"Mickey","last_name":"Mouse"}')

但我的要求是合并查询

表架构 架构部分1

架构部分2

架构部分3

Answer 1

尝试以下代码

MERGE INTO  "W_TESTTABLE"  t
USING (
SELECT *,parse_json(ATTRIBUTES) as ATTRIBUTES_NEW 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 ("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" )) s
ON (t."ID" = s."ID" ) 
WHEN matched THEN UPDATE 
set              T."ATTRIBUTES"=s."ATTRIBUTES_NEW", 
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_NEW", 
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"
)