SQL check is zero or null

Question

Hi i have some problem

Need to check is null or zero by column if something wrong doing some algorithm

This is a table:

col1    col2    col3    col4
1        0      3376    0
2       600     null    14468.5714
3       null    0       0
4       600     3376    null

COALESCE doesn't work with zero "0" value, case its too huge

need to realize some of that

, CAST(COALESCE(col2, (col3/7), (col4/30)) as money) col2
, CAST(COALESCE(col3, (col2*7), (col4/30*7))as money) col3
, CAST(COALESCE(col4, (col3/7*30),(col2*30))as money) col4 

how to solve this in fastest way. ThanX

Answer 1

While COALESCE allows you to replace a NULL with a specific value, NULLIF will allow you to replace a specific value with a NULL. You could use the latter on 0 and end up with something like this:

, CAST(
    COALESCE(
      NULLIF(col2, 0),
      NULLIF(col3, 0) / 7,
      NULLIF(col4, 0) / 30
    ) AS money
  ) AS col2
, CAST(
    COALESCE(
      NULLIF(col3, 0),
      NULLIF(col2, 0) * 7,
      NULLIF(col4, 0) / 30 * 7)
    ) AS money
  ) AS col3
, CAST(
    COALESCE(
      NULLIF(col4, 0),
      NULLIF(col3, 0) / 7 * 30,
      NULLIF(col2, 0) * 30
    ) AS money
  ) AS col4

Still rather long, if you asked me, but definitely shorter than using CASEs. The last NULLIF in each expression might be unnecessary, I left them there for consistency. Perhaps you could add a fourth argument of 0 everywhere, just to make sure the result is never a NULL.

Answer 2

Why not just use a CASE condition like

CASE WHEN col2 is not null and col2 <> 0 THEN your_calculation