Consider NULL as 0

Asked

Viewed 395 times

0

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:

inserir a descrição da imagem aqui

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

  • 1

    I don’t know how you tried to implement the IsNull, but it is really the output. Correct would be: PIVOT (SUM(ISNULL(SQ.VALOR_TRANSACAO,0)) FOR ..... Being the IsNull within the SUM.

  • I tried using isnull but it didn’t work Tried how? Like this: ISNULL(valor_transacao,0 )?

  • That’s how I tried "PIVOT (SUM(ISNULL(sq.valor_transacao,0)) FOR num_transacao IN ( [3], [5], [7])) AS pt " gives me the following error: Message 102, Level 15, State 1, Line 29 Incorrect syntax next to '('.

  • Could be some syntax feature of PIVOT. In that case, it solves for you if you use the ISNULL in the internal select: (...), ISNULL(finmovem.valor_transacao, 0) as valor_transacao (...)

  • didn’t work, he’s not doing the sum correctly

1 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)

Browser other questions tagged

You are not signed in. Login or sign up in order to post.