SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 What is wrong with this SQL statement?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

texas1992
Starting Member

21 Posts
Posted - 02/15/2013 :  11:03:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1511 Posts
Posted - 02/15/2013 :  11:39:43  Show Profile  Reply with Quote
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 - 02/15/2013 :  14:45:36  Show Profile  Reply with Quote
The angle brackets < > are there simply to show where the errors are occuring.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3831 Posts
Posted - 02/15/2013 :  16:03:27  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000