DB2 query not working

Question

Customer (parent table)

Id	Charge Amt
C1	10
C2	20
C3	30
C4	40

Payment (child table)

Id	pymt amt
C1	10
C2	5
C4	40

Select Id and pending balance from customer and payment tables

Pending balance validations:

1. If payment amount is >= charge amount then set pending balances to 0
2. If payment amount is less then set charge amount - payment amount to pending balance.
3. If payment record not found then set charge amount to pending balances.

My query is not working for third(3) scenario:

I need output like:

Id	Pending bal
C1	0
C2	15
C3	30
C4	0

Query: Tried left outer join also but sum of payment is not working.

Select A.ID,
       (SELECT (
        CASE
        WHEN (SUM(B.PYMT AMT) < A.CHARGE AMT)
        THEN (A.CHARGE AMT - SUM(B.PYMT AMT))
        ELSE 0 END)
        FROM PYMT B
        WHERE B.ID = A.ID)
FROM CUST A;

Answer 1

A simple case without any aggregation function is needed.

select c.Id,
       case when p.pymtamt >= c.ChargeAmt then 0
            when p.pymtamt <  c.ChargeAmt then c.ChargeAmt-p.pymtamt
            when p.pymtamt is null then c.ChargeAmt end as Pendingbal
from Customer c
left join Payment p on c.Id=p.Id;

1.If payment amount is >= charge amount then set pending balances to 0

when p.pymtamt >= c.ChargeAmt then 0

2.If payment amount is less then set charge amount - payment amount to pending balance.

when p.pymtamt <  c.ChargeAmt then c.ChargeAmt-p.pymtamt

3.If payment record not found then set charge amount to pending balances.

when p.pymtamt is null then c.ChargeAmt

pymtamt when doesn't exist will be null because of the left join.

Note, the demo is tested using MySQL , when choosing DB2 it didn't display any records

Answer 2

In case of multiple payments for the same customer.

select 
  c.Id
, decode (sign (c.ChargeAmt - coalesce(p.pymtamt, 0)), -1, 0, c.ChargeAmt - coalesce(p.pymtamt, 0))
  as Pendingbal
from Customer c
left join 
(
  select Id, sum (pymtamt) as pymtamt
  from Payment
  group by Id
) p on c.Id=p.Id
order by c.Id

ID	PENDINGBAL
C1	0
C2	15
C3	30
C4	0

fiddle