Joined: 26 Nov 2002 Posts: 12370 Topics: 75 Location: San Jose
Posted: Thu Sep 15, 2011 1:50 pm Post subject:
Celeste Locklin,
Are they 5 different rows or data corresponding to 1 just 1 row of the table?
I am guessing it is just a single row
Code:
CD1 CD2 CD3 CD4 CD5 AMT1 AMT2 AMT3 AMT4 AMT5
K 9 D 106.84 125.74 103.16 0.00 0.00
If that is the case then you can use the following untested sql
Code:
SELECT CASE WHEN PMT_CD1 IN ('C','D','K','3')
THEN PMT_AMT_1 ELSE DEC(0) END +
CASE WHEN PMT_CD2 IN ('C','D','K','3')
THEN PMT_AMT_2 ELSE DEC(0) END +
CASE WHEN PMT_CD3 IN ('C','D','K','3')
THEN PMT_AMT_3 ELSE DEC(0) END +
CASE WHEN PMT_CD4 IN ('C','D','K','3')
THEN PMT_AMT_4 ELSE DEC(0) END +
CASE WHEN PMT_CD5 IN ('C','D','K','3')
THEN PMT_AMT_5 ELSE DEC(0) END
FROM Table
;
Joined: 04 May 2006 Posts: 26 Topics: 10 Location: USA
Posted: Fri Sep 16, 2011 3:45 pm Post subject:
you are the best ! I had to do a little tweaking, adding sum, digits, and dec() but it works
Code:
SELECT DIGITS(SUM
(CASE WHEN PMT_CD_1 IN ('C ','D ','K ','3 ')
THEN DEC(PMT_AMT_1) ELSE DEC(0) END +
CASE WHEN PMT_CD_2 IN ('C','D','K','3')
THEN DEC(PMT_AMT_2) ELSE DEC(0) END +
CASE WHEN PMT_CD_3 IN ('C','D','K','3')
THEN DEC(PMT_AMT_3) ELSE DEC(0) END +
CASE WHEN PMT_CD_4 IN ('C','D','K','3')
THEN DEC(PMT_AMT_4) ELSE DEC(0) END +
CASE WHEN PMT_CD_5 IN ('C','D','K','3')
THEN DEC(PMTAMT_5) ELSE DEC(0) END ) )
FROM table
WHERE . . .
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
