Compare NULL using case SQL

Question

Compare NULL using case SQL

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

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 did a test with the acronym of one of the managers and it returns the text correctly.

I appreciate the help.

2

sql null case

Author: Taisbevalle, 2017-05-08

Source

1 answers

score 5 · Accepted Answer

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

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