Compare NULL using CASE SQL

Question

Compare NULL using CASE SQL

Navigation

#1 by (1 votes)

2

I have to retrieve 2 fields in this query (number of projects and the management that performs such a project). This query is going to be used to generate a report in JASPER and it does not display field with NULL value, so I want to for every null in column f.br_nome_ger_executo the query return "manage not informed". This query is working, but it 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 managements and it returns the text correctly.

Thanks for the help.

sql null case

asked by anonymous 08.05.2017 / 16:14

1 answer

TabPanel does not work with angular Fit picture when uploading

score 1 · Accepted Answer

To compare a null value you should 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 ISNULL function:

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.