How to get a single output in SQL based on column values in snowflake-CodePudding

I have a query that returns a single column, STATUS which can have 2 distinct values SUCCESS and FAILED. So, sometimes it just returns SUCCESS or FAILURE or SUCCESS and FAILURE both. My requirement is to write a SQL query for snowflake to return 1 when the value is only SUCCESS and return 0 when the value is FAILURE or when both SUCCESS and FAILURE.

CodePudding user response：

you can use the CASE statment,

select CASE WHEN COLUMN1 = 'SUCESS' THEN 1
ELSE 0 END ret_value from 
values 
('SUCESS'), ('FAILURE'), ('SUCCESS and FAILURE');

CodePudding user response：

I think you want conditional aggregation. Feel to rewrite iff using case if you prefer so

Scenario 1

with cte(col)as

(select 'SUCCESS' union all
 select 'FAILURE')
 
 select min(iff(col='SUCCESS',1,0)) as status
 from cte;

Scenario 2

with cte(col)as

(select 'SUCCESS' union all
 select 'SUCCESS')
 
 select min(iff(col='SUCCESS',1,0)) as status
 from cte