CASE statement not Defining a Column Correctly Snowflake

user_5278594 0
Viewed 365

I have a query in following format, used to perform COALESCE as well as define a new column using CASE statement.

SELECT ....
       COALESCE(mm1,'missing') AS mm1,
       COALESCE(mm2,'missing') AS mm2,

    CASE WHEN mm1='false' AND mm2='false' THEN 'No-Proxy'
         WHEN mm1 IN ('false','missing') AND mm2='true' THEN 'Good-Proxy'
         WHEN mm1 ='true' AND mm2 IN ('false','missing') THEN 'Bad-Proxy'
         WHEN ((mm1='true' AND mm2='true') OR (mm1='missing' AND mm2='missing')
              OR (mm1='false' AND mm2='missing') OR (mm1='missing' AND mm2='false')) THEN 'Unknown'
         END AS Proxy_Type,

As seen above when both mm1 and mm2 are originally NULL, we need to put value as Unknown for Proxy_Type. But when we run the query, we get unexpected output. Plz see screenshot. enter image description here

Kindly advise on how to fix it.

snowflake-cloud-data-platform case
2 Answers

It seems that "inline/lateral column aliasing" does not allow to "override" column at the same level:

CREATE OR REPLACE TABLE t
AS SELECT NULL AS mm1, NULL AS mm2;

Option 1: Using different column alias

SELECT 
       COALESCE(mm1,'missing') AS mm1_,
       COALESCE(mm2,'missing') AS mm2_,

    CASE WHEN mm1_='false' AND mm2_='false' THEN 'No-Proxy'
         WHEN mm1_ IN ('false','missing') AND mm2_='true' THEN 'Good-Proxy'
         WHEN mm1_ ='true' AND mm2_ IN ('false','missing') THEN 'Bad-Proxy'
         WHEN ((mm1_='true' AND mm2_='true') OR (mm1_='missing' AND mm2_='missing')
              OR (mm1_='false' AND mm2_='missing') 
              OR (mm1_='missing' AND mm2_='false')) THEN 'Unknown'
         END AS Proxy_Type
FROM t;
-- MM1_ MM2_    PROXY_TYPE
--missing   missing Unknown

Option 2: LATERAL JOIN and prefixing with subquery alias:

SELECT -- t.mm1, t.mm2,
    s.mm1, s.mm2,
    CASE WHEN s.mm1='false' AND s.mm2='false' THEN 'No-Proxy'
         WHEN s.mm1 IN ('false','missing') AND s.mm2='true' THEN 'Good-Proxy'
         WHEN s.mm1 ='true' AND s.mm2 IN ('false','missing') THEN 'Bad-Proxy'
         WHEN ((s.mm1='true' AND s.mm2='true') OR (s.mm1='missing' AND s.mm2='missing')
              OR (s.mm1='false' AND s.mm2='missing') 
              OR (s.mm1='missing' AND s.mm2='false')) THEN 'Unknown'
         END AS Proxy_Type
FROM t,
LATERAL(SELECT COALESCE(t.mm1,'missing') AS mm1,COALESCE(t.mm2,'missing') AS mm2) s;
-- MM1  MM2 PROXY_TYPE
--missing   missing Unknown

The ideal situation would be if we had additional keyword to distinguish between original column and calculated expression, kind of SAS - calculated.

SELECT 
     col, 
     col+10 AS col, 
     col,
     calculated col
FROM t;
-- output
t.col/expression/t.col/expression
user_50708790

I’m guessing you are trying to use the re-defined values of mm1/mm2 in your case statement? If so then SQL doesn’t work like that, values don’t change within the same select statement so m1/m2 will have their starting values wherever they are referenced in the select statement.

One way round this is to use something like this:

COALESCE(mm1,'missing') AS mm1,
COALESCE(mm2,'missing') AS mm2,

CASE WHEN COALESCE(mm1,'missing') ='false' …
user_136583990
Related