Incorrect Case Syntax - 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

Transact-SQL (2000)

Incorrect Case Syntax

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2011-06-03 : 14:43:48

I have the following query:


SELECT[ScratchPad5].EmployeeNumber
,  SUM CASE WHEN code in ('Vacation','Holiday','Sicktime', 'ETO') THEN 0
            ELSE CASE WHEN code < 40 THEN code ELSE 40 END
      END) AS TotalRegHours
,  SUM CASE WHEN code in ('Vacation','Holiday','Sicktime', 'ETO') THEN 0
            ELSE CASE WHEN code < 40 THEN 0    ELSE code - 40 END
      END) AS TotalOT
,  Sum(CASE WHEN 'Vacation' = code THEN [sumhours] END) AS Vacation
,  Sum(CASE WHEN 'Holiday' = code THEN [sumhours] END)  AS Holiday
,  Sum(CASE WHEN 'Sicktime' = code THEN [sumhours] END) AS Sicktime
,  Sum(CASE WHEN 'ETO' = code THEN [sumhours] END)      AS ETO
   FROM ScratchPad5
   GROUP BY [ScratchPad5].EmployeeNumber
   ORDER BY [ScratchPad5].EmployeeNumber

and I'm getting an incorrect syntax error
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'CASE'.

I'm sure I'm missing something small. Can someone else tell me what it may be?

Thank you

Doug

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-03 : 15:10:43

You can't subtract 40 from ('Vacation','Holiday','Sicktime', 'ETO') and
('Vacation','Holiday','Sicktime', 'ETO') < 40 is also a no-no.

Jim

Everyday I learn something that somebody else already knew

dougancil
Posting Yak Master

217 Posts

Posted - 2011-06-03 : 15:16:32

I missed some braces in this. Sorry for opening this topic.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources