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.
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.