Postgres-case when

Question

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 did the above script in Postgres 9.4, but if the credit limit field is null it does not add with another limit of notes (spun). It seems I have to zero the field that comes as null, I was thinking about using case when, but it makes a mistake. Someone can help me?

Answer 1

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

Try to replace:

coalesce(sum(valor),0) as pendencia

For:

sum(coalesce(valor,0)) as pendencia

But if you still prefer to use the CASE WHEN:

sum(CASE WHEN valor IS NULL THEN 0 ELSE valor END) as pendencia

Answer 2

Use the coalesce:

Coalesce(p.limite_credito,0) as limite_credito

This function returns the first parameter that is not null. In your case, when p.limite_credito is null, will return 0.

You can also use it with more parameters:

Coalesce(p1,p2,p3,p4)

More information on: https://www.postgresql.org/docs/9.2/static/functions-conditional.html