Consider NULL as 0

Question

In the following query I need the transaction value to be considered as 0 when it is NULL in the highlighted aggregation, how can it be done? I tried using isnull but it didn’t work

SELECT num_empenho AS EMP, data_empenho AS DATA, nome_fornecedor AS FORNECEDOR, codigo_tipo AS CODIGO_TIPO, codigo_orgao AS CODIGO_ORGAO, valor_empenhado AS EMPENHADO, valor_anulado AS ANULADO, [3] AS LIQ_PERIODO, valor_liquidado AS LIQUID_ACUM, [5] AS PAGO_PERIODO,[7] AS PAGO_PERIODO2, valor_pago AS PAGO_ACUMUL, sld_a_liquidar AS A_LIQUIDAR, sld_a_pagar AS LIQ_A_PAGAR, cod_reduzido 
FROM ( 
         SELECT finmovem.num_transacao 
                ,finmovem.codigo_tipo 
                ,finmovem.valor_transacao 
                ,finmovem.codigo_orgao 
                ,finmovem.data_transacao
                ,finempe.cod_reduzido  
                ,finempe.data_empenho 
                ,finmovem.num_empenho 
                ,finempe.nome_fornecedor 
                ,finempe.valor_empenhado 
                ,finempe.valor_anulado 
                ,finempe.valor_pago 
                ,finempe.valor_liquidado 
                ,cast(finempe.valor_empenhado as decimal(18,2)) - cast(finempe.valor_anulado as decimal(18,2)) - cast(finempe.valor_liquidado as decimal(18,2)) as sld_a_liquidar 
                ,cast(finempe.valor_liquidado as decimal(18,2)) - cast(finempe.valor_pago as decimal(18,2)) as sld_a_pagar 
                ,cast(orcdotac.sld_orc_ano as decimal(18,2)) + cast(orcdotac.sld_orc_vinc as decimal(18,2)) + cast(orcdotac.sld_supl_ano as decimal(18,2)) + cast(orcdotac.sld_esp_ano as decimal(18,2)) + cast(orcdotac.sld_ext_ano as decimal(18,2)) - cast(orcdotac.sld_re_ano as decimal(18,2)) as saldo_dot_ant 
         FROM finmovem 
         INNER JOIN finempe ON 
               (finempe.num_empenho = finmovem.num_empenho) 
         INNER JOIN orcdotac ON 
               (finempe.cod_reduzido = orcdotac.cod_reduzido)  
         WHERE finmovem.codigo_tipo = 1 and finempe.codigo_tipo = 1 and finmovem.codigo_orgao = 02 and finempe.codigo_orgao = 02 and finmovem.data_transacao between '1-1-2002' and '31-12-2002' and finempe.data_empenho between '1-1-2002' and '31-12-2002'
         GROUP BY finmovem.valor_transacao, finmovem.num_transacao, finmovem.codigo_orgao, finempe.cod_reduzido, finmovem.num_empenho, finempe.data_empenho, finempe.nome_fornecedor, finempe.valor_empenhado,
         finempe.valor_anulado, finempe.valor_pago, finempe.valor_liquidado, orcdotac.sld_orc_ano, orcdotac.sld_orc_vinc, orcdotac.sld_supl_ano, orcdotac.sld_esp_ano, orcdotac.sld_ext_ano,
         orcdotac.sld_re_ano, finmovem.codigo_tipo ,finmovem.data_transacao 
     ) sq 
PIVOT (SUM(sq.valor_transacao) FOR num_transacao IN ( [3], [5], [7])) AS pt <<< nessa agregação 
ORDER BY cod_reduzido, num_empenho

in the column where num_transacao is 3 it returns as follows:

Making the SUM of it there should return 4000 but is returning 1000.

Answer 1

Use COALESCE. This SQL function takes a list of n parameters and returns the first non-null value it finds. Below follows a small example:

create table tabela_exemplo
(
   descricao varchar(20) not null
  ,valorA decimal(18,2) null
  ,valorB decimal(18,2) null
)

GO

insert into tabela_exemplo
(descricao, valorA, valorB)
values
 ('A e B não nulos', 1,2)
,('A e B não nulos', 3,4)
,('A e B não nulos', 5,6)
,('A nulos B com valor', null,8)
,('A nulos B com valor', null,10)
,('A nulos B com valor', null,12)
,('tudo nulo', null,null)
,('tudo nulo', null,null)
,('tudo nulo', null,null)

GO

select te.descricao, te.valorA, te.valorB, coalesce(te.valorA, te.valorB, 42) as [Esta coluna nunca retorna null]
from dbo.tabela_exemplo te

/* Exemplo com aggregate */ 
select te.descricao, sum(te.valorA), sum(te.valorB), sum(coalesce(te.valorA, te.valorB, 42)) as [Este somatório nunca retorna null]
from dbo.tabela_exemplo te
group by te.descricao

Upshot:

descricao            valorA  valorB  Esta coluna nunca retorna null
-------------------- ------- ------- ------------------------------
A e B não nulos      1.00     2.00    1.00
A e B não nulos      3.00     4.00    3.00
A e B não nulos      5.00     6.00    5.00
A nulos B com valor  NULL     8.00    8.00
A nulos B com valor  NULL    10.00   10.00
A nulos B com valor  NULL    12.00   12.00
tudo nulo            NULL     NULL   42.00
tudo nulo            NULL     NULL   42.00
tudo nulo            NULL     NULL   42.00

(9 linhas afetadas)

descricao            Sum(A) Sum(B) Este somatório nunca retorna null
-------------------- ------ ------ ---------------------------------
A e B não nulos       9.00  12.00    9.00
A nulos B com valor   NULL  30.00   30.00
tudo nulo             NULL   NULL  126.00


Warning: Null value is eliminated by an aggregate or other SET operation.

(3 linhas afetadas)