INPUT

Clabcd no C_Chck C_Typ A_AMT B_AMT
222222222222200 1 1 50 40
222222222222201 4 1 -20 -10
3333333333333300 2 2 12 22
3333333333333302 3 2 -10 -15

OUTPUT

SUBSTR(Clabcdno, 1, 13) C_Chck C_Typ AMOUNT_PAID
2222222222222 1 1 50
2222222222222 4 1 -20
33333333333333 2 2 22
33333333333333 3 2 -15


Input comes as 15 digits for clabcd no but we take only the first 13 digits SUBSTR(Clabcdno, 1, 13)
For C_Typ =1, A_AMT= 50 20 = 30 which is > 0 so it it displayed in the output ( for SUBSTR(Clabcdno, 1, 13) = 2222222222222)
For C_Typ = 2, B_AMT = 22-15 = 7 which is > 0 so it is displayed in the output ( for SUBSTR(Clabcdno, 1, 13) = 33333333333333)

How do i check this condition in the search criteria (Where clause)? Input is 4 records, i need to sum the amount fields which would make it one record, but still i need all the 4 records in the output.
subquery might help if only one SUM field is there i guess. Here 2 SUM fields A_AMT and B_AMT are used and also subquery would cause performance issue.

I already have 2 left outer joins and one union. This is an additional change needed for the query.


Any help is appreciated. Thanks