Compare NULL using CASE SQL

Question

I have to recover 2 fields in this query (number of projects and the management that runs such a project). This query will be used to generate a report in JASPER and it does not display field with value NULL, so I want every null in the column f.br_nome_ger_executo the query returns "manage not informed". This query is working, but displays the null in the response to the consultation.

select  count(i.is_active) NUMERO_PROJETOS,
case f.br_nome_ger_executo
      when null then 'Gerencia nao informada'
      else f.br_nome_ger_executo end as GERENCIA_EXECUTORA
from inv_investments i
join odf_ca_project f on i.id = f.id  and i.is_active = 1
group by f.br_nome_ger_executo

I made a test with the acronym of one of the management and it returns the text correctly.

I appreciate the help.

Answer 1

To compare a null value you must use it in WHEN with IS NULL:

SELECT COUNT(i.is_active) AS NUMERO_PROJETOS,
       CASE
         WHEN f.br_nome_ger_executo IS NULL THEN 'Gerencia nao informada'
         ELSE f.br_nome_ger_executo
       END AS GERENCIA_EXECUTORA
  FROM inv_investments i
 INNER JOIN odf_ca_project f
    ON i.id = f.id
   and i.is_active = 1
 GROUP BY f.br_nome_ger_executo

Another way to get the desired result is to use the function ISNULL:

SELECT COUNT(i.is_active) AS NUMERO_PROJETOS,
       ISNULL(f.br_nome_ger_executo, 'Gerencia nao informada') AS GERENCIA_EXECUTORA
  FROM inv_investments i
 INNER JOIN odf_ca_project f
    ON i.id = f.id
   AND i.is_active = 1
 GROUP BY f.br_nome_ger_executo

ISNULL

Replaces NULL with the specified replacement value.

---

Here you give a brief explanation of why the initial solution did not work: Why NULL values are not selected?