|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Be the architects of evolution and help create the mobile internet future. It’s your move---enter to win here! |
|
#1
March 24th, 2004, 10:54 AM
|
|||
|
|||
|
Need help on SQL statement
I am trying to get subtotals and grand total figures on total_res and actual_hours_posted using a case statement and am having problems. Can you help me? See below in Blue. Thanks!
SELECT p.unique_name project_code, p.name project_name, tp.prstart fiscal_week_start, tp.prfinish fiscal_week_finish, count(distinct a.prresourceid) as total_res, cc.prname charge_code, SUM(DECODE(ts.prstatus,4,(NVL(te.practsum,0)/3600),0)) actual_posted_hours, sum(case when substr(p.name,1,5) = 'Admin%' then (actual_posted_hours + 0) as fiscal_week_admin_hours End
|
|
#2
March 24th, 2004, 11:36 AM
|
|||
|
|||
|
case expressions are not supported in all versions of Oracle. Why can't you use decode?
|
|
#4
March 24th, 2004, 01:56 PM
|
|||
|
|||
|
Code:
sum(decode(substr(p.name,1,5) ,'Admin' ,actual_posted_hours ,0) as fiscal_week_admin_hours I spotted an error in your case expression syntax Code:
sum(case when substr(p.name,1,5) = 'Admin' then actual_posted_hours else 0 end) as fiscal_week_admin_hours
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > Need help on SQL statement |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
