CODEWITHSUNDEEP
nulldb2coalesce
tkyass
tkyass

Reputation: 3186

DB2 change Null to zeros using coalesce with pivot

I'm trying to select a value from a table like the following example:

user  id_1  id_2  id_3
-----------------------
a      1     NULL   3
b      1     NULL   NULL
c      NULL  NULL   3
d      NULL  2      NULL

I'm using the following pivot to come up with the above result:

select user,
       max(case when id = 1 then id end) as id_1,
       max(case when id = 2 then id end) as id_2,
       max(case when id = 3 then id end) as id_3
from table t
group by user
order by user;

I want to change the Nulls to zeros to have the following result:

user  id_1  id_2  id_3
-----------------------
a      1     0      3
b      1     0      0
c      0     0      3
d      0     2      0

I used COALESCE like the following:

select user,
       max(case when id = 1 then COALESCE(id,0) end) as id_1,
       max(case when id = 2 then COALESCE(id,0) end) as id_2,
       max(case when id = 3 then COALESCE(id,0) end) as id_3
from table t
group by user
order by user;

but it didn't work, any advice?

Upvotes: 1

Views: 533

Answers (2)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

Mugos solution is probably the best way to do it, so this is just to show that there are other ways to do it as well. In you query COALESCE never (assuming id can't be null) has any effect since if case evaluates to true -> id (which I assume can not be null), if case evaluates to false COALESCE is not applied. As Mugo demonstrate you can solve this by adding an else to your case. Another way is to apply COALESCE outside of CASE:

select user,
   COALESCE(max(case when id = 1 then id end),0) as id_1,
   ...

FWIW, the other variant of CASE is a bit shorter:

select user,
   COALESCE(max(case id when 1 then id end),0) as id_1,
   ...

Upvotes: 0

Muqo
Muqo

Reputation: 414

Each CASE statement is evaluating to NULL when the WHEN isn't satisfied. And MAX() returns NULL when all values are NULL. If you simply want to get 0 instead of NULL, put else 0 before each end.

select user,
       max(case when id = 1 then id else 0 end) as id_1,
       max(case when id = 2 then id else 0 end) as id_2,
       max(case when id = 3 then id else 0 end) as id_3
from table t
group by user
order by user;

Upvotes: 3

Related Questions