Snowflake Merge using streams

Question

Merge statement throws:

"Boolean value is not recognized"

I'm reading all varchar values from streams and writing to a master table. I dont have any Boolean in the source or destination table. Unable to find out why I'm getting: "Boolean value is not recognized" Error."

create table "EMP_TEST"  (EMPID integer, EMPNAME VARCHAR(500), EMPADD VARCHAR(500), EMPSALARY INTEGER);
create table "EMP_TEST_MAIN"  (EMPID integer, EMPNAME VARCHAR(500), EMPADD VARCHAR(500), EMPSALARY INTEGER);

create or replace stream ST_EMP_TEST on table "EMP_TEST";

insert into "EMP_TEST" 
select 1, 'AAA','PLACE 1', 100 UNION
select 2, 'BBB','PLACE 2', 200 UNION
select 3, 'CCC','PLACE 3', 300;

MERGE INTO  "EMP_TEST_MAIN" AS T USING (select * from ST_EMP_TEST where NOT (METADATA$ACTION ='DELETE' AND METADATA$ISUPDATE = TRUE)) AS S ON T.EMPID = S.EMPID WHEN MATCHED AND S.METADATA$ACTION = 'INSERT' AND S.METADATA$ISUPDATE THEN  UPDATE SET T.EMPNAME = S.EMPNAME AND T.EMPADD = S.EMPADD AND T.EMPSALARY = S.EMPSALARY WHEN MATCHED AND S.METADATA$ACTION = 'DELETE' THEN 
    DELETE WHEN NOT MATCHED AND S.METADATA$ACTION = 'INSERT' THEN      
    INSERT (T.EMPID, T.EMPNAME, T.EMPADD, T.EMPSALARY)   VALUES (S.EMPID, S.EMPNAME, S.EMPADD, S.EMPSALARY);

Without actual code it is impossible to resolve it. Maybe one of the condition in WHEN clause that is not valid boolean test. — Lukasz Szozda
– Lukasz Szozda, Commented Apr 29, 2021 at 15:36

Lukasz Szozda · Accepted Answer · 2021-04-29 15:47:09Z

1

The columns in UPDATE part are separated with AND:

WHEN MATCHED AND S.METADATA$ACTION = 'INSERT' AND S.METADATA$ISUPDATE 
  THEN  UPDATE SET T.EMPNAME = S.EMPNAME 
        AND T.EMPADD = S.EMPADD 
        AND T.EMPSALARY = S.EMPSALARY 
        -- AND is incorrect in this context

Should be ,:

WHEN MATCHED AND S.METADATA$ACTION = 'INSERT' AND S.METADATA$ISUPDATE 
  THEN  UPDATE SET T.EMPNAME = S.EMPNAME 
        ,T.EMPADD = S.EMPADD
        ,T.EMPSALARY = S.EMPSALARY

answered Apr 29, 2021 at 15:47

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MKS_AWS Over a year ago

Thank you and shame one me for overlooking such a simple thing.

Lukasz Szozda Over a year ago

@MKS_AWS No worries, btw I would also refactor AND METADATA$ISUPDATE = TRUE as = TRUE is superficial(column is already boolean)

Collectives™ on Stack Overflow

Snowflake Merge using streams

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related