CODEWITHSUNDEEP
sqlpostgresql
msw
msw

Reputation: 11

Is there an efficient way/function to handle combinations of nulls in an equation in SQL?

Say I have a column total in my table, which is built upon an equation total = w - (x + y + z) where w, x, y , and z are all columns as well, and they are all from different tables I'm left joining. There are cases where x is null, y is null, or z is null. There are also cases in which x and y are null, y and z are null, and so on until covering all possible combinations of nulls in the equation.

Currently, I'm handling this by using a CASE clause and a lot of WHENs. Here's an example snippet:

WHEN (x IS NOT NULL) 
     AND (y IS NOT NULL)
     AND (z IS NOT NULL) THEN (w - (x + y + z))
WHEN (x IS NOT NULL)
     AND (y IS NOT NULL)
     AND (z IS NULL) THEN (w - (x + y))
WHEN (x IS NOT NULL)
     AND (y IS NULL)
     AND (z IS NOT NULL) THEN (w - (x + z))
...

My problem is that I am working with more than 4 columns, resulting in a pretty large number of these combinations. While I could just write out all the combinations with a bunch of WHENs (this is what I've been doing), it takes a really long time and I'd love to know if there's a more efficient way of accomplishing the task, perhaps a function I could write.

Upvotes: 1

Views: 39

Answers (1)

FLSte
FLSte

Reputation: 668

Yes! You can use the COALESCE function

select
  (w - (coalesce(x,0) + coalesce(y,0) + coalesce(z,0))) as Total
from tbl

COALESCE will return the first non-null value in the list. So in the above case, if x,y or z are null, zero will be returned in their place.

Upvotes: 4

Related Questions