Calculating 2 columns, if one column is zero, the result of calculation is NULL

I am trying to calculate 2 columns and the calculation is correct if both columns have value greater than zero. If at least one column is zero, the result is null. Why?

Here is the part of query:

SELECT c.interval, 
       CASE WHEN b.naziv_upnika_sodna IS NULL 
            THEN table2.naziv_upnika_predsodna 
            ELSE b.naziv_upnika_sodna          END AS naziv_upnika, 
       CASE WHEN b.placila_sodna IS NULL 
            THEN 0 
            ELSE b.placila_sodna               END AS placila_sodna, 
       CASE WHEN b.preplacilo_sodna IS NULL 
            THEN 0 
            ELSE b.preplacilo_sodna            END AS preplacilo_sodna, 
       CASE WHEN b.pokrivanje_sodna IS NULL
            THEN 0 
            ELSE b.pokrivanje_sodna            END AS pokrivanje_sodna, 
       CASE WHEN table2.placila_predsodna IS NULL 
            THEN 0 
            ELSE table2.placila_predsodna      END AS placila_predsodna, 
       CASE WHEN table2.preplacilo_predsodna IS NULL 
            THEN 0 
            ELSE table2.preplacilo_predsodna   END AS preplacilo_predsodna, 
       CASE WHEN table2.pokrivanje_predsodna IS NULL 
            THEN 0 
            ELSE table2.pokrivanje_predsodna   END AS pokrivanje_predsodna, 
       b.placila_sodna   table2.placila_predsodna

Thanks for your answers.

CodePudding user response：

Because NULL represents an unknown value.

Adding an unknown value to a known value always results in an unknown value.

You can remove all those case expressions and use coalesce().

CASE WHEN b.naziv_upnika_sodna IS NULL 
THEN table2.naziv_upnika_predsodna 
ELSE b.naziv_upnika_sodna END AS naziv_upnika

becomes

Coalesce(b.naziv_upnika_sodna, table2.naziv_upnika_predsodna) AS naziv_upnika

and

CASE WHEN b.placila_sodna IS NULL THEN 0 
ELSE b.placila_sodna  END AS placila_sodna

becomes

Coalesce(b.placila_sodna, 0) AS placila_sodna

etc.

Use only the coalesced values in your calculation and you'll resolve the NULLs

CodePudding user response：

The problem in your query is that you can't reference the aliases of the CASE statements (actually any aliases) in their same scope. You either need:

a subquery:

SELECT my_subquery.placila_sodna   my_subquery.placila_predsodna
FROM ( SELECT c.interval, 
              CASE WHEN b.naziv_upnika_sodna IS NULL 
                   THEN table2.naziv_upnika_predsodna 
                   ELSE b.naziv_upnika_sodna          END AS naziv_upnika, 
              CASE WHEN b.placila_sodna IS NULL 
                   THEN 0 
                   ELSE b.placila_sodna               END AS placila_sodna, 
              CASE WHEN b.preplacilo_sodna IS NULL 
                   THEN 0 
                   ELSE b.preplacilo_sodna            END AS preplacilo_sodna, 
              CASE WHEN b.pokrivanje_sodna IS NULL
                   THEN 0 
                   ELSE b.pokrivanje_sodna            END AS pokrivanje_sodna, 
              CASE WHEN table2.placila_predsodna IS NULL 
                   THEN 0 
                   ELSE table2.placila_predsodna      END AS placila_predsodna, 
              CASE WHEN table2.preplacilo_predsodna IS NULL 
                   THEN 0 
                   ELSE table2.preplacilo_predsodna   END AS preplacilo_predsodna, 
              CASE WHEN table2.pokrivanje_predsodna IS NULL 
                   THEN 0 
                   ELSE table2.pokrivanje_predsodna   END AS pokrivanje_predsodna
      ...
     ) my_subquery

to replicate the CASE statements in your local scope (difference in the last row):

SELECT c.interval, 
       CASE WHEN b.naziv_upnika_sodna IS NULL 
            THEN table2.naziv_upnika_predsodna 
            ELSE b.naziv_upnika_sodna          END AS naziv_upnika, 
       CASE WHEN b.placila_sodna IS NULL 
            THEN 0 
            ELSE b.placila_sodna               END AS placila_sodna, 
       CASE WHEN b.preplacilo_sodna IS NULL 
            THEN 0 
            ELSE b.preplacilo_sodna            END AS preplacilo_sodna, 
       CASE WHEN b.pokrivanje_sodna IS NULL
            THEN 0 
            ELSE b.pokrivanje_sodna            END AS pokrivanje_sodna, 
       CASE WHEN table2.placila_predsodna IS NULL 
            THEN 0 
            ELSE table2.placila_predsodna      END AS placila_predsodna, 
       CASE WHEN table2.preplacilo_predsodna IS NULL 
            THEN 0 
            ELSE table2.preplacilo_predsodna   END AS preplacilo_predsodna, 
       CASE WHEN table2.pokrivanje_predsodna IS NULL 
            THEN 0 
            ELSE table2.pokrivanje_predsodna   END AS pokrivanje_predsodna, 
       CASE WHEN b.placila_sodna          IS NULL THEN 0 ELSE b.placila_sodna END 
         CASE WHEN table2.placila_predsodna IS NULL THEN 0 ELSE table2.placila_predsodna END

As a side note, I'd recommend using the construct already mentioned in Stu's answer, which is cleaner to see. In that case, the code would become as follows:

SELECT c.interval, 
       COALESCE(b.naziv_upnika_sodna       , 0) AS naziv_upnika        ,
       COALESCE(b.placila_sodna            , 0) AS placila_sodna       ,
       COALESCE(b.preplacilo_sodna         , 0) AS preplacilo_sodna    ,
       COALESCE(b.pokrivanje_sodna         , 0) AS pokrivanje_sodna    ,
       COALESCE(table2.placila_predsodna   , 0) AS placila_predsodna   ,
       COALESCE(table2.preplacilo_predsodna, 0) AS preplacilo_predsodna,
       COALESCE(table2.pokrivanje_predsodna, 0) AS pokrivanje_predsodna,
       COALESCE(b.placila_sodna, 0)   COALESCE(table2.placila_predsodna, 0)