Snowflake case statement is returning an error instead of the value specified within the ELSE clause

user_3922727 0
Viewed 433

I need to check if one or many fields already exists in a table so I can do a merge into statement using them.

I tried this:

select sat_sector_hkey,
CASE 
    WHEN EXISTS(select id from hub_sector)
    THEN (MERGE INTO ...)
END AS id
from sat_sector;

For testing, I used only one case statement, and replaced merge into with a THEN...ELSE values:

SELECT sat_sector_hkey,
CASE 
    WHEN EXISTS(select id from hub_sector)
    THEN '1'
    ELSE ''
END AS id
FROM sat_sector;

When this field does not exists, the query return an error instead of '':

SQL compilation error: error line 3 at position 23 invalid identifier 'ID'

I am using a CASE, because I need to check if a column exists or not, as I don't know if it exists or not due to some technicalities in our data coming from multiple sources.

snowflake-cloud-data-platform case merge sql
1 Answers

Try this:

  • Construct an object with the full row.
  • Test if the constructed object has data for "ID".
create or replace temp table maybe_id
as 
select 1 x, 2 id;

select *, 
case 
   when object_construct(a.*):ID is not null
   then '1'
   else ''
end as id 
from maybe_id a
;

Works for me - it gives 1 when the column id has data, and `` when the column doesn't exist in the table.

user_1324380
Related