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 Lukasz Szozda · Accepted Answer · 2021-04-29T15:47:09

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

Snowflake Merge using streams

1 Answers