Using Case in an Order By Statement

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 2000 Forums

Transact-SQL (2000)

Using Case in an Order By Statement

Author

Topic

Bookerdog
Starting Member

4 Posts

Posted - 2002-11-05 : 11:42:27

A few caveats.
1. I'm pretty new to stored procedures, so go easy.
2. This is SQL Server 2000
3. Right now I'm coding this using Visual Studio .NET. This particular machine doesn't have SQL Client installed, so unfortunately the errors not nearly as verbose as I'd like.

Basically, when I try and save this procedure I get a "This Operation Could Not Be Completed" error from the .Net Enviroment, which is a synonym for "There's a syntax error but we can't tell you where."

Anyway, I know the syntax error is in the CASE statement in the ORDER BY clause, because everything is just fine if I remove that statement. So, can any of you tell me why this doesn't work? I've tried using various names and data types for the @ORDERBY Variable, but none have worked.

ALTER PROCEDURE dbo.PictureSubCategoryLookup
(
@STARTPICDATE as smalldatetime ='1/1/1980',
@ENDPICDATE as smalldatetime = '12/31/2050',
@STARTADDDATE as smalldatetime ='1/1/1980',
@ENDADDDATE as smalldatetime = '12/31/2050',
@SUBCATEGORY as int,
@ORDERBY char(15) = 'DateLoaded'
)

AS

SELECT DISTINCT
Pictures.PicId,
Pictures.HasThumb,
Pictures.ThumbFileName,
Pictures.ShortDesc,
Pictures.DateTaken,
Pictures.TakenBy,
Pictures.DateLoaded,
Pictures.FileSize

FROM
Pictures
INNER JOIN
PictureCategories
ON Pictures.PicId = PictureCategories.PicId

WHERE
Pictures.MakeAvailable = 1
AND PictureCategories.SubCategoryId = @SUBCATEGORY
AND (Pictures.DateTaken BETWEEN @STARTPICDATE AND @ENDPICDATE)
AND (Pictures.DateLoaded BETWEEN @STARTADDDATE AND @ENDADDDATE)

ORDER BY
CASE
WHEN @ORDERBY = 'DateLoaded'
THEN Pictures.DateLoaded
ELSE Pictures.DateTaken
END

1fred
Posting Yak Master

158 Posts

Posted - 2002-11-05 : 12:16:51

I'm not sure but maybe a case statement can't be place in an order by clause. I know your query could work with dynamic SQL, do a search on the forum or site, there is plenty of stuff on it.

Lavos
Posting Yak Master

200 Posts

Posted - 2002-11-05 : 12:39:43

CASE statements Do work in an order by clause. Using CASE in order by's allows for some impressive sorting and ordering.

Usually I would've guessed that the two columns that you are switching between are incompatible types, but it looks like both those columns are datetime's. If all the columns that can be returned by the CASE statement are not identical types, or types that can be implicitly converted to each other, than the statement will fail.

Try this version of the ORDER BY and see if it works


ORDER BY
CASE
WHEN @ORDERBY = 'DateLoaded'
THEN Pictures.DateLoaded
END,
CASE
WHEN @ORDERBY <> 'DateLoaded'
THEN Pictures.DateTaken
END

Does the procedure actually exist in the database? Are you sure you are running the script on the right database? (though, it looks like it is running fine without the CASE so who knows.)

Other than that, it looks like it has proper syntax and runs fine on my own machine.

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

Edited by - Lavos on 11/05/2002 12:47:27

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-11-05 : 12:43:28

If you have a distinct it is not possible to order by anything that is not included in the select list.

SELECT DISTINCT
Pictures.PicId,
Pictures.HasThumb,
Pictures.ThumbFileName,
Pictures.ShortDesc,
Pictures.DateTaken,
Pictures.TakenBy,
Pictures.DateLoaded,
Pictures.FileSize ,
CASE
WHEN @ORDERBY = 'DateLoaded'
THEN Pictures.DateLoaded
ELSE Pictures.DateTaken
END as orderCriteria

FROM
Pictures
INNER JOIN
PictureCategories
ON Pictures.PicId = PictureCategories.PicId

WHERE
Pictures.MakeAvailable = 1
AND PictureCategories.SubCategoryId = @SUBCATEGORY
AND (Pictures.DateTaken BETWEEN @STARTPICDATE AND @ENDPICDATE)
AND (Pictures.DateLoaded BETWEEN @STARTADDDATE AND @ENDADDDATE)

ORDER BY orderCriteria

1fred:

case expressions can be used in an order by. A case statements in SQL is something else.

Bookerdog
Starting Member

4 Posts

Posted - 2002-11-05 : 12:45:41

Yes, both columns are datetime, and even so, I tried it with the two separate case statements, and still no dice.

It looks right to me too. I'm thinking it's a .NET Engine parser issue. I'll try it tonight on the SQL Server box at home and see what happens.

Lavos
Posting Yak Master

200 Posts

Posted - 2002-11-05 : 12:49:03

DOH, sniped on editing my reply. I missed the DISTINCT in the SELECT part. Bah.

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

Bookerdog
Starting Member

4 Posts

Posted - 2002-11-05 : 12:49:39

LarsG,

Thanks, I wondered if it was the distinct. However, you say you can't order by anything not in the select list, but both of those fields are in the select list, are they not? Or is it that you just can't switch between them dynamically because of the nature of the cursor.

Anyway, I will try your suggestion. However, one further caveat. How would I add a switch for Ascending vs. Descending in this same situation. Can I do that in the ORDER BY section with a case statement?

Now to go try a few things.

Bookerdog
Starting Member

4 Posts

Posted - 2002-11-05 : 13:01:18

Bingo, works like a charm, but I haven't figured out an ascending/decending switch on the orderCriteria field. Is it possible?

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-11-05 : 13:05:44

Something like this maybe

select * from (
SELECT DISTINCT
Pictures.PicId,
Pictures.HasThumb,
Pictures.ThumbFileName,
Pictures.ShortDesc,
Pictures.DateTaken,
Pictures.TakenBy,
Pictures.DateLoaded,
Pictures.FileSize

FROM
Pictures
INNER JOIN
PictureCategories
ON Pictures.PicId = PictureCategories.PicId

WHERE
Pictures.MakeAvailable = 1
AND PictureCategories.SubCategoryId = @SUBCATEGORY
AND (Pictures.DateTaken BETWEEN @STARTPICDATE AND @ENDPICDATE)
AND (Pictures.DateLoaded BETWEEN @STARTADDDATE AND @ENDADDDATE) )s

ORDER BY
case @sortOrder
when 'ASC' then
CASE WHEN @ORDERBY = 'DateLoaded' tHEN Pictures.DateLoaded
ELSE Pictures.DateTaken end end asc,
case @sortOrder
when 'DESC' then
CASE WHEN @ORDERBY = 'DateLoaded' tHEN Pictures.DateLoaded
ELSE Pictures.DateTaken end end desc

You should use the term case expression, it is not a statement.

I see your point about both column being part of the select list but sql server does not enter into that type of analysis, the general case is quite intimidating.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources