雪花：合并查询：需要VARIANT，但获得列的VARCHAR(3038) [英] Snowflake: Merge query: expecting VARIANT but got VARCHAR(3038) for column

问题描述

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"}')....

但在VALUES子句中出现错误[PARSE_JSON(‘{"TYPE"："Issue__c"，"url"："/services/data/v42.0/sobjects/issue__c/a0E2v00001jiirTEAQ"}’)]in VALUES：

我能够成功执行插入查询

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

但我的要求是合并查询

表架构 Schemapart1

Schemapart2

Schemapart3

推荐答案

尝试以下代码

 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"
       )  

这篇关于雪花：合并查询：需要VARIANT，但获得列的VARCHAR(3038)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！