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 2012 Forums
 Transact-SQL (2012)
 What is wrong with this SQL statement?

Author  Topic 

texas1992
Starting Member

21 Posts

Posted - 2013-02-15 : 11:03:46
I have this SQL statement that works fine until I add the first CASE statement. Then I get error at the places marked with < >.

Here are the errors I get:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'Select'.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'ELSE'.

Select <FO.timekeeperid>,
Case WHEN <FO.[description]> = 'Storage' THEN <Select> FloorplanLegendColors.Color from FloorplanLegendColors Where DepartmentID = 999 ELSE FPLC.Color End AS Color,
FPLC.Color,
FO.XPosition,FO.YPosition,FO.ObjectID,FO.[Name],
Case WHEN VW.CasualNameByFirst IS NULL OR VW.CasualNameByFirst = '' THEN FO.[Name] ELSE VW.CasualNameByFirst end AS CasualNameByFirst,
FO.[description]
From (<FloorplanObjects> AS FO Left Join dbo.vwEmployeeBasic AS VW ON fo.TimeKeeperID = vw.TimeKeeperID) Left Join dbo.FloorplanLegendColors AS FPLC ON vw.DepartmentID = FPLC.DepartmentID
Where fo.Active = 'Y' and fo.FloorID = 29 and fo.LayoutName = 'Test 1 B'

ANy help would be greatly appreciated.
Thanks.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-15 : 11:39:43
What are those angle brackets for? Do the column names really have the angle brackets? And, then there are some angle brackets that just are there, which should not be there. See in red:
Select <FO.timekeeperid>, 
Case WHEN <FO.[description]> = 'Storage' THEN <Select> FloorplanLegendColors.Color from FloorplanLegendColors Where DepartmentID = 999 ELSE FPLC.Color End AS Color,
FPLC.Color,
FO.XPosition,FO.YPosition,FO.ObjectID,FO.[Name],
Case WHEN VW.CasualNameByFirst IS NULL OR VW.CasualNameByFirst = '' THEN FO.[Name] ELSE VW.CasualNameByFirst end AS CasualNameByFirst,
FO.[description]
From (<FloorplanObjects> AS FO Left Join dbo.vwEmployeeBasic AS VW ON fo.TimeKeeperID = vw.TimeKeeperID) Left Join dbo.FloorplanLegendColors AS FPLC ON vw.DepartmentID = FPLC.DepartmentID
Where fo.Active = 'Y' and fo.FloorID = 29 and fo.LayoutName = 'Test 1 B'
If the column name is indeed <FO.timekeeperid>, you should escape the name with square brackets like this: [<FO.timekeeperid>]
Go to Top of Page

texas1992
Starting Member

21 Posts

Posted - 2013-02-15 : 14:45:36
The angle brackets < > are there simply to show where the errors are occuring.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-15 : 16:03:27
Try wrapping the SELECT statement in the CASE expression in parenthsis:

Select 
<FO.timekeeperid>,
Case
WHEN <FO.[description]> = 'Storage'
THEN (<Select> FloorplanLegendColors.Color from FloorplanLegendColors Where DepartmentID = 999)
ELSE FPLC.Color
End AS Color,
Go to Top of Page
   

- Advertisement -