Postgres-case when

Navigation
0
select x.codigo,x.nome,x.cpf,x.contato,
x.limite_credito,
x.pendencia as pendencia

 from

( select p.codigo,p.cpf,p.contato,p.limite_credito,p.grid,p.grupo,p.nome,
p.flag,p.tipo,m.child,m.pessoa,coalesce(sum(valor),0) as pendencia from 
movto m join pessoa p on m.pessoa=p.grid 

where m.child=0 and m.data between'2017-05-01' and '2017-05-05' and m.conta_debitar='1.3.03' 
group by m.pessoa,m.child,p.tipo,p.flag,p.nome,p.limite_credito,
p.grupo,p.grid,p.codigo,p.cpf
) as x

left join pessoa_conta pc on pc.pessoa=x.pessoa 
join grupo_pessoa gp on gp.grid=x.grupo
join pessoa pe on pe.grid=x.grid
and x.nome!='CONSUMIDOR FINAL' 
and x.child=0
and x.flag='A'
AND X.tipo='CU'
group by x.nome,x.pendencia,x.tipo,x.codigo,x.cpf,x.contato,pc.lim_credito,x.limite_credito
order by 1

I made the above script in Postgres 9.4, but if the credit limit field is null it does not add up with another note limit (spin). It seems that I have to zero the field that comes with null , I was thinking of using case when , but it gives error. Can someone help me?

    
asked by anonymous 07.05.2017 / 04:17

2 answers

0

Your aggregation logic with sum and coalesce seems to me to be incorrect.

Try to replace: 

coalesce(sum(valor),0) as pendencia

By: 

sum(coalesce(valor,0)) as pendencia

But if you still prefer to use CASE WHEN : 

sum(CASE WHEN valor IS NULL THEN 0 ELSE valor END) as pendencia
    
08.05.2017 / 13:05
-1

Use coalesce : 

Coalesce(p.limite_credito,0) as limite_credito

This function returns the first non-null parameter. In your case, when p.limite_credito is null, it will return 0.

You can also use it with more parameters: 

Coalesce(p1,p2,p3,p4)

More information at: link

    
07.05.2017 / 04:52
Problem in recovering data from firebase While in While PHP Pdo [closed]