Case in SQL returning two values instead of one

Question

I'm trying to return one value for two conditions in SQL CASE statement

CASE  
WHEN Col1 = 'false' OR Col1 IS NULL  THEN 'NA' 
ELSE ...
END

Data

Col1
-----
false
poor
moderate
null

In the query result, I'm getting two times "NA" repeatedly. I'm expecting a one-time "NA"

Output

Col1
-----
NA
poor
moderate
NA

Expected Output:

Col1
-----
NA
poor
moderate

Can you share some of your data you are working with and the expected output — SelVazi, Dec 22 '22 at 08:16
Most people here want sample table data and the expected result as formatted text, not as images (or links to images.) — jarlh, Dec 22 '22 at 08:25
*I'm getting two times "NA" repeatedly.* Provide complete code. CASE operator **cannot** produce "doubled" output even in theory.. — Akina, Dec 22 '22 at 08:28
Why is there 1 less row in your expected result than in your source data? DO you just want one NA even if there are many which meet the condition for setting NA? — P.Salmon, Dec 22 '22 at 08:39

score 0 · Answer 1 · answered Dec 22 '22 at 08:45

UNION where first select picks NA and second select picks the others

select 'NA' as col1 from t where col1 is null or col1 ='false'
union 
select col1 from t where col1 is not null and col1 <> 'false'

UNION comes with and implied distinct so the outcome is

+----------+
| col1     |
+----------+
| NA       |
| poor     |
| moderate |
+----------+
3 rows in set (0.002 sec)

score 0 · Answer 2 · answered Dec 22 '22 at 08:56

0

So you want DISTINCT results?

SELECT DISTINCT CASE WHEN col1 <> 'false' THEN col1 ELSE 'NA' END AS col1
FROM   tbl;

With a simplified CASE expression, too. See:

answered Dec 22 '22 at 08:56

2 Answers2