What is wrong with this CASE 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

General SQL Server Forums

New to SQL Server Programming

What is wrong with this CASE statement?

Author

Topic

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-05-19 : 20:38:42

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

549 Posts

Posted - 2013-05-19 : 22:09:20

[CODE]

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

GO

[/CODE]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-19 : 22:12:37

[code]
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
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-20 : 01:19:13

[code]
SELECT BusinessEntityID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag
WHEN 1 THEN BusinessEntityID
WHEN 0 THEN -1 * BusinessEntityID
END DESC
[/code]
assuming BusinessEntityID is of numeric type
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-20 : 11:45:04

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.

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-05-20 : 15:59:33

quote:
Originally posted by MuMu88

[CODE]

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

GO

[/CODE]

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?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-20 : 16:16:04

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?

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-05-20 : 23:27:28

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.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-21 : 00:26:41

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-21 : 04:59:39

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-21 : 11:02:17

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources