Syntax Error - 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)

Syntax Error

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2011-01-10 : 15:29:15

I have the following query that I'm trying to run but I'm having a syntax issue. I know that it's something simple that I'm missing so any help would be appreciated. The query is:

SELECT [EmployeeNumber],[Name],SUM([Minutes]) AS summinutes,SUM([Minutes])/60 AS sumhours into scratchpad5
SUM(CASE WHEN Cat=2 THEN [Minutes] ELSE 0 END) AS specminutes
FROM
(
SELECT [EmployeeNumber],[Name],[Dateonly],[Minutes],1 AS Cat
FROM Scratchpad2
where dateonly between '11/1/2010' and '11/8/2010'
UNION ALL
SELECT [EmployeeNumber],[Name],[ExceptionDate],[TotalMinutes],2
FROM ScratchPad4
)t
GROUP BY [EmployeeNumber],[Name]

and the syntax error I'm getting is:

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'SUM'.
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near 't'.

Thank you

Doug

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-10 : 15:38:36

Well you've got "into scratchpad5" and then another SUM. You can't do that. INTO must come directly before the FROM.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-10 : 15:39:17

[code]
SELECT [EmployeeNumber],[Name],SUM([Minutes]) AS summinutes,SUM([Minutes])/60 AS sumhours ~~into scratchpad5~~
SUM(CASE WHEN Cat=2 THEN [Minutes] ELSE 0 END) AS specminutes

into scratchpad5
FROM
(
SELECT [EmployeeNumber],[Name],[Dateonly],[Minutes],1 AS Cat
FROM Scratchpad2
where dateonly between '11/1/2010' and '11/8/2010'
UNION ALL
SELECT [EmployeeNumber],[Name],[ExceptionDate],[TotalMinutes],2
FROM ScratchPad4
)t
GROUP BY [EmployeeNumber],[Name]

[/code]

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-10 : 15:40:33

Oops, hit send instead of preview. You'll also need a comma before last sum
,SUM(CASE WHEN Cat=2 THEN [Minutes] ELSE 0 END) AS specminutes

Jim

Everyday I learn something that somebody else already knew

dougancil
Posting Yak Master

217 Posts

Posted - 2011-01-10 : 15:42:40

Thanks Jim and Tara, I knew it was something simple. Must be a case of the "Mondays."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources