If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Help with using Case statement in a calculation

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 09-30-03, 18:28
bencheng bencheng is offline
Registered User
 
Join Date: Sep 2003
Posts: 10
Help with using Case statement in a calculation

I am having trouble using the case statement in a calculation. I want to get the sum amount if the sum of pct >1 using pct_cur if pct_cur is not null or by pct_orig if pct_cur is null. there are multiple records of pct_cur and pct_orig. So if in one record pct_cur is null, I want to use pct_orig for the sum of pct:

example:
loan number amount pct_cur pct_orig
100 1000 .5 .3
100 1000 null .4
100 1000 .2 .1

so in the above case the sum of pct would be 1.1 (.5+.4+.2) so how do I put that in SQL?

I tried
select sum(amount)
from table1
where sum(pct)>1
case
when pct_cur is not null
then pct_cur
when pct_cur is null
then pct_orig
else null
end as pct

can someone please tell me the proper way to do this?
any help would be much appreciated.
Reply With Quote
  #2 (permalink)  
Old 09-30-03, 19:48
chuzhoi chuzhoi is offline
Registered User
 
Join Date: Dec 2002
Posts: 134
Re: Help with using Case statement in a calculation

Check the docs for coalesce function:
select sum(coalesce(pct_cur, pct_orig))
from table1
Reply With Quote
  #3 (permalink)  
Old 10-01-03, 04:51
blom0344 blom0344 is offline
Registered User
 
Join Date: Jan 2003
Location: Zutphen,Netherlands
Posts: 256
You should have a good look at the SQL cookbooks in order to get better grip of SQL syntax. (As Chuzhoi recommended)

If you are using an aggregate combined with case you wrap the aggregate around the case statement like:

select sum(case when pct_pur is null then pct_orig else pct_pur end)

coalesce is spot-on for your specific 'case'. You can spare 3 positions by using the 'VALUE' function (same as coalesce)
__________________
Ties Blom
Senior Application Developer BI
Getronics Healthcare
DB2,ORACLE,Powercenter,BusObj,Access,
SQL, SQL server
Reply With Quote
  #4 (permalink)  
Old 10-01-03, 15:41
bencheng bencheng is offline
Registered User
 
Join Date: Sep 2003
Posts: 10
Thanks guys,
actually I am a CPA (finance guy) and just started using sql last week to do some analysis..... so just making progress pretty slowly
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On