DECODE or CASE in SQL Server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DECODE or CASE in SQL Server

Author

Topic

matpj
Starting Member

8 Posts

Posted - 2006-07-27 : 09:28:45

Hi all,

I usually use Oracle databases, but have a query on a SQL Server database that I need to amend.

in Oracle I use the DECODE function to satisfy my current problem.
Can this be used in SQL in the same way? as i'm having problems.

my original code is:



SELECT projects.title, 
WIPUNION.Client, 
WIPUNION.Project, 
Sum (WIPUNION.Quantity)

FROM 
DSTRMPROD.dbo.projects projects, 
(SELECT ppa_WIP.Client, ppa_WIP.Project, ppa_WIP.Quantity, ppa_WIP.Chargeable as chargeable FROM dbo.ppa_WIP ppa_WIP 
WHERE ppa_WIP.Client LIKE 'LGL02UK' AND ppa_WIP.Status=0 Union ALL SELECT ppa_TRANS.Client, ppa_TRANS.Project, ppa_TRANS.Quantity, 2 as chargeable FROM dbo.ppa_TransControl ppa_TRANS WHERE ppa_TRANS.Client LIKE 'LGL02UK' )WIPUNION 

WHERE 
WIPUNION.Project = PROJECTS.projid 

Group BY 
WIPUNION.Client, 
WIPUNION.Project, 
PROJECTS.Title, 
WIPUNION.Chargeable

i was hoping to use the function:


 SELECT projects.title, 
WIPUNION.Client, 
WIPUNION.Project, 
Sum (WIPUNION.Quantity),
sum(DECODE(WIPUNION.chargeable, 1, WIPUNION.QUANTITY)) as Charge,
sum(DECODE(WIPUNION.chargeable, 0, WIPUNION.QUANTITY)) as NonCharge

it doesn't seem to work.
can anyone tell me if i'm going down the wrong path?

alternativley I DID try
:


CASE WHEN WIPUNION.chargeable = 1 then SUM(WIPUNION.QUANTITY)
Else 0 end

etc..

but this didn't work either..

thanks in advance,
Matt

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-27 : 09:35:57

sum(case when WIPUNION.chargeable = 1 then WIPUNION.QUANTITY else 0 end) as Charge,
sum(case when WIPUNION.chargeable = 0 then WIPUNION.QUANTITY else 0 end) as NonCharge

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

matpj
Starting Member

8 Posts

Posted - 2006-07-27 : 10:30:22

thanks! that works great!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources