What is wrong with this SQL statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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>]

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.

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,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources