If a value in a column is not either A or B, then replace that value with "NO".
dataset
ColA ColB
A L
NULL P
B M
C G
Expected Output:
ColA ColB
A L
NO P
B M
NO G
I tried this but no luck:
SELECT *,
CASE WHEN ColA NOT IN(A,B) REPLACE (ColA, ColA, 'NO')
ELSE ColA
END ColA
FROM dataset;
It returns a column of all "NO".
Your code, should not compile, so I guess it is not the actual code.
(1) It is missing THEN after WHEN.
(2) The values A and B should be enclosed in single quotes, otherwise they would be recognized as column names.
So it should be written as:
SELECT *,
CASE
WHEN ColA NOT IN('A','B') THEN REPLACE(ColA, ColA, 'NO')
ELSE ColA
END NewColA
FROM dataset;
This would produce the desired results unless there is a NULL value in ColA.
In case of NULL, ColA NOT IN('A','B') would evaluate to NULL and no replacement would take place.
You can write the statement using Snowflake's IFF() function like this:
SELECT *, IFF(ColA IN ('A', 'B'), ColA, 'NO') NewColA
FROM dataset;
In case ColA IN ('A', 'B') evaluates to NULL then the FALSE part of IFF() is returned which is 'NO'
replace() is not needed just use case expression :
select d.*, (case when cola in ('a', 'b')
then cola else 'NO'
end)
from dataset d;
Another way of doing this...
SELECT Col1, Col2, ColN,
CASE
WHEN ColA = 'A' OR ColA = 'B' THEN ColA
ELSE NULL
END AS ColA
FROM dataset
SELECT
CASE WHEN ColA NOT LIKE 'A' OR 'B' THEN 'NO' ELSE ColA END AS 'ColA'
,ColB
FROM Dataset;
To be on the safe side you most likely also need to add a IS NULL check also.
i believe SQL standards defined NOT IN() should not work with NULL values..
As ColA NOT IN('A','B') is the same as writting ColA <> 'A' OR ColB <> 'B' and SELECT 'A' <> NULL should never not be true.
Query
SELECT
(
CASE
WHEN ColA NOT IN('A','B') OR ColA IS NULL
THEN 'NO'
ELSE ColA
END
) AS ColA
, ColB
FROM
dataset
