Case in SQL returning two values instead of one-CodePudding

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

CodePudding user response：

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)

CodePudding user response：

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: