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
 General SQL Server Forums
 New to SQL Server Programming
 What is wrong with this CASE statement?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rock_query
Yak Posting Veteran

52 Posts

Posted - 05/19/2013 :  20:38:42  Show Profile  Reply with Quote
I am using the AdventureWorks2012 database for SQL Server Express on my PC.

The error is:

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'WHEN'.

Here is my code:

SELECT BusinessEntityID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag
WHEN 1 THEN BusinessEntityID END DESC,
WHEN 0 THEN BusinessEntityID END
GO

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/19/2013 :  22:09:20  Show Profile  Reply with Quote


SELECT BusinessEntityID, SalariedFlag
FROM HumanResources.Employee
ORDER BY 
CASE SalariedFlag
WHEN 1 THEN BusinessEntityID END DESC,
CASE SalariedFlag
WHEN 0 THEN BusinessEntityID END

GO

Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
942 Posts

Posted - 05/19/2013 :  22:12:37  Show Profile  Reply with Quote

SELECT BusinessEntityID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag
WHEN 1 THEN BusinessEntityID ELSE 0 END DESC,
CASE SalariedFlag
WHEN 0 THEN BusinessEntityID ELSE 0 END
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/20/2013 :  01:19:13  Show Profile  Reply with Quote

SELECT BusinessEntityID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag
WHEN 1 THEN BusinessEntityID 
WHEN 0 THEN -1 * BusinessEntityID
END DESC

assuming BusinessEntityID is of numeric type
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 05/20/2013 01:19:43
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4354 Posts

Posted - 05/20/2013 :  11:45:04  Show Profile  Reply with Quote
Just to clarify that Visakh is showing. You can't have the ASC/DESC as part of the case expression like that, it has to come after.
Go to Top of Page

Rock_query
Yak Posting Veteran

52 Posts

Posted - 05/20/2013 :  15:59:33  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88



SELECT BusinessEntityID, SalariedFlag
FROM HumanResources.Employee
ORDER BY 
CASE SalariedFlag
WHEN 1 THEN BusinessEntityID END DESC,
CASE SalariedFlag
WHEN 0 THEN BusinessEntityID END

GO





This might be what Lamprey was referring to but I want to be sure. On the Microsoft website, here is a sample of the Simple format for a CASE statement.

USE AdventureWorks2012;
GO
SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
GO

Notice there is one CASE and multiple WHENs. I am also using a CASE statement in simple format, so I listed the word CASE once followed by more than one WHEN statement in my code, just like the code above from the Microsoft website, but that didn't work. Why would you have to list CASE a second time for the second WHEN statement?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4354 Posts

Posted - 05/20/2013 :  16:16:04  Show Profile  Reply with Quote
The reason for two cases, is that you need one case for sorting ascenting and one case for sorting descending. In that example the ASC is left off the end of the second case expression (as that is teh default sort order). There is also some default values that may not be obvious.
SELECT 
	BusinessEntityID
	, SalariedFlag
FROM 
	HumanResources.Employee
ORDER BY 
	CASE SalariedFlag WHEN 1 THEN BusinessEntityID ELSE NULL END DESC,
	CASE SalariedFlag WHEN 0 THEN BusinessEntityID ELSE NULL END ASC
Does that make sense?

Also, are you having trouble with a case expression in a different location? One, in the select clause and not in an order by clause?

Edited by - Lamprey on 05/20/2013 16:23:24
Go to Top of Page

Rock_query
Yak Posting Veteran

52 Posts

Posted - 05/20/2013 :  23:27:28  Show Profile  Reply with Quote
quote:
Originally posted by Lamprey

The reason for two cases, is that you need one case for sorting ascenting and one case for sorting descending. In that example the ASC is left off the end of the second case expression (as that is teh default sort order). There is also some default values that may not be obvious.
SELECT 
	BusinessEntityID
	, SalariedFlag
FROM 
	HumanResources.Employee
ORDER BY 
	CASE SalariedFlag WHEN 1 THEN BusinessEntityID ELSE NULL END DESC,
	CASE SalariedFlag WHEN 0 THEN BusinessEntityID ELSE NULL END ASC
Does that make sense?

Also, are you having trouble with a case expression in a different location? One, in the select clause and not in an order by clause?



Thank you Lamprey for your reply. I think that helps.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 05/21/2013 :  00:26:41  Show Profile  Reply with Quote
quote:
Originally posted by Lamprey

Just to clarify that Visakh is showing. You can't have the ASC/DESC as part of the case expression like that, it has to come after.


Hi Lamprey,
As per Visakh's solution, that solution exactly same as OP's requirement... For ascending order he made BusinessEntityId as negative so that it will order by ascending

ORDER BY CASE SalariedFlag
WHEN 1 THEN BusinessEntityID ---------- here Descending
WHEN 0 THEN -1 * BusinessEntityID ----------- This is Ascending because of -1 Multiplication
END DESC

Note: Only the limitation is that BusinessEntityID should be numeric type

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/21/2013 :  04:59:39  Show Profile  Reply with Quote
quote:
Originally posted by Lamprey

Just to clarify that Visakh is showing. You can't have the ASC/DESC as part of the case expression like that, it has to come after.


yep indeed. CASE is just an expression which gives you return value based on numerous condition checks. It cant have any effect on control flow of the program.
thats why i did a small hack to keep the sort direction same but change values to negative to get ascending effect

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4354 Posts

Posted - 05/21/2013 :  11:02:17  Show Profile  Reply with Quote
quote:
Originally posted by bandi

quote:
Originally posted by Lamprey

Just to clarify that Visakh is showing. You can't have the ASC/DESC as part of the case expression like that, it has to come after.


Hi Lamprey,
As per Visakh's solution, that solution exactly same as OP's requirement... For ascending order he made BusinessEntityId as negative so that it will order by ascending

ORDER BY CASE SalariedFlag
WHEN 1 THEN BusinessEntityID ---------- here Descending
WHEN 0 THEN -1 * BusinessEntityID ----------- This is Ascending because of -1 Multiplication
END DESC

Note: Only the limitation is that BusinessEntityID should be numeric type

--
Chandu

Yep, fully agreed. I was just trying to make it clear why the DESC didn't work inside the case expression and thus why there were two separate case expressions instead of one with mutiple where conditions.
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.08 seconds. Powered By: Snitz Forums 2000