Microsoft SQL Server forum - Using CASE .. WHEN to have 'dynamic' sort

Author

Using CASE .. WHEN to have 'dynamic' sort

Jozef de Veuster

2005-07-25, 9:29 am

Hi,

I'm trying to create a Stored Procedure that returns a recordset, but I
want to be able to choose the ORDER BY clause in mijn parameter list of
the Stored Procedure. Since CASE .. WHEN can only be used in the SELECT
clause, I came up with the following:

-- BEGIN SCRIPT --
DECLARE @blah AS VARCHAR(20)
SET @blah = 'DOSSIER_CODE'
SELECT DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,
requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE, COUNT(NOTE_PID)
NrOfNotes,
CASE @blah
WHEN 'DOSSIER_CODE'
THEN DOSSIER_CODE
WHEN 'SCAN_DATE'
THEN SCAN_DATE
ELSE
SCAN_DATE
END AS ORDERFIELD
FROM MR_DOCS
LEFT OUTER JOIN MR_USERS
ON MR_DOCS.USER_FID = USER_PID
LEFT OUTER JOIN MR_DOSSIERS
ON DOSSIER_FID = DOSSIER_PID
LEFT OUTER JOIN MR_NOTES
ON DOC_PID = MR_NOTES.DOC_FID
WHERE MR_DOCS.USER_FID = 1
AND DOC_STATE IN (1, 3, 4)
AND REMINDER_DATE <= getdate()
AND MR_DOCS.isVisible = 1
AND TREE_FID IS NULL
-- Added by Tim Derdelinckx - 2005.06.20
AND TODO_FID IS NULL
-- Select documents that are scanned for this user (1),
-- or moved to this user (3),
-- or forwarded to this user (4),
GROUP BY DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,
requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE
UNION
SELECT DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,
requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE, COUNT(NOTE_PID)
NrOfNotes,
CASE @blah
WHEN 'DOSSIER_CODE'
THEN DOSSIER_CODE
WHEN 'SCAN_DATE'
THEN SCAN_DATE
ELSE
SCAN_DATE
END AS ORDERFIELD
FROM MR_DOCS
LEFT OUTER JOIN MR_USERS
ON USER_FID = USER_PID
LEFT OUTER JOIN MR_DOSSIERS
ON DOSSIER_FID = DOSSIER_PID
LEFT OUTER JOIN MR_NOTES
ON DOC_PID = MR_NOTES.DOC_FID
WHERE BORROW_USER_FID = 1
AND DOC_STATE = 5
AND REMINDER_DATE <= getdate()
AND MR_DOCS.isVisible = 1
AND TREE_FID IS NULL
-- Added by Tim Derdelinckx - 2005.06.20
AND TODO_FID IS NULL
-- or borrowed to this user
GROUP BY DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,
requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE
ORDER BY ORDERFIELD DESC
-- END SCRIPT --

But it doesn't seem to work correctly:
When SET @blah = 'SCAN_DATE', it works just fine!

When SET @blah = 'DOSSIER_CODE':
I get an error: Server: Msg 242, Level 16, State 3, Line 3
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
Warning: Null value is eliminated by an aggregate or other SET
operation.

Anyone any ideas about this? Or maybe another way of handling this (not
with CASE .. WHEN)?

Thanks a lot,
Tim@Allgeier

*** Sent via Developersdex http://www.droptable.com ***

Erland Sommarskog

2005-07-25, 9:29 am

Jozef de Veuster (nospam@devdex.com) writes:
> I'm trying to create a Stored Procedure that returns a recordset, but I
> want to be able to choose the ORDER BY clause in mijn parameter list of
> the Stored Procedure. Since CASE .. WHEN can only be used in the SELECT
> clause, I came up with the following:

Ehum, you can say things like:

SELECT ...
FROM ....
ORDER BY CASE @blah .... END

> CASE @blah
> WHEN 'DOSSIER_CODE'
> THEN DOSSIER_CODE
> WHEN 'SCAN_DATE'
> THEN SCAN_DATE
> ELSE
> SCAN_DATE
> END AS ORDERFIELD
>
> When SET @blah = 'DOSSIER_CODE':
> I get an error: Server: Msg 242, Level 16, State 3, Line 3
> The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
> Warning: Null value is eliminated by an aggregate or other SET
> operation.
>
> Anyone any ideas about this? Or maybe another way of handling this (not
> with CASE .. WHEN)?

A CASE expression always returns the same data type. If the different
branches have different data types, they are converted according to
the data-type precendence order, which is described in Books Online under
"datatypes" in the T-SQL Reference. In this case here varchar has lower
precendence than datetime, so SQL Server attempts to convert the varchar
column to datetime.

This can be addressed in two ways. One is to add explicit converts
for the date columns:

convert(varchar, SCAN_DATE, 121)

(form 121 is YYYY-MM-DD HH:MM:SS.fff)

The other is two have more than one sort column:

ORDER BY CASE @blah WHEN 'DOSSIER_CODE' THEN DOSSIER_CODE END,
CASE @blan WHEN 'DOSSIER_CODE' THEN NULL
ELSE SCAN_DATE
END

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Jozef de Veuster

2005-07-25, 9:29 am

Okay, Thanks a lot Erland!
I couldn't find anything about using CASE .. WHEN in the ORDER BY in the
Transact SQL, therefore ...

Thanks,
Tim@Allgeier

*** Sent via Developersdex http://www.droptable.com ***

Jozef de Veuster

2005-07-25, 9:29 am

Just for fun I tried your example using CASE..WHEN in the ORDER BY, but
I get the error:
Server: Msg 104, Level 15, State 1, Line 3
ORDER BY items must appear in the select list if the statement contains
a UNION operator.

It seems like the result of the CASE statement is a bit transformed, so
SQLServer doesn't recognize that field is already in the SELECT list. (I
tried with the DOSSIER_CODE column, without CAST or CONVERT)

Tim@Allgeier

*** Sent via Developersdex http://www.droptable.com ***

Jozef de Veuster

2005-07-25, 9:29 am

For the people interested, here is the solution I used:

DECLARE @sortField AS VARCHAR(20)
DECLARE @UserId AS INTEGER
SET @sortField = 'SCAN_DATE'
SET @UserId = 1

SELECT DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,
requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE, COUNT(NOTE_PID)
NrOfNotes,
CASE @sortField
WHEN 'DOSSIER_CODE'
THEN CAST(DOSSIER_CODE AS VARCHAR(50))
WHEN 'SCAN_DATE'
THEN CONVERT(VARCHAR, SCAN_DATE, 121)
ELSE
CAST(SCAN_DATE AS VARCHAR(50))
END AS ORDERFIELD
FROM MR_DOCS
LEFT OUTER JOIN MR_USERS
ON MR_DOCS.USER_FID = USER_PID
LEFT OUTER JOIN MR_DOSSIERS
ON DOSSIER_FID = DOSSIER_PID
LEFT OUTER JOIN MR_NOTES
ON DOC_PID = MR_NOTES.DOC_FID
WHERE MR_DOCS.USER_FID = @UserId
AND DOC_STATE IN (1, 3, 4)
AND REMINDER_DATE <= getdate()
AND MR_DOCS.isVisible = 1
AND TREE_FID IS NULL
AND TODO_FID IS NULL
-- scanned for this user (1), moved to this user (3), forwarded to this
user (4),
GROUP BY DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,
requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE
UNION
SELECT DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,
requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE, COUNT(NOTE_PID)
NrOfNotes,
CASE @sortField
WHEN 'DOSSIER_CODE'
THEN CAST(DOSSIER_CODE AS VARCHAR(50))
WHEN 'SCAN_DATE'
THEN CONVERT(VARCHAR, SCAN_DATE, 121)
ELSE
CAST(SCAN_DATE AS VARCHAR(50))
END AS ORDERFIELD
FROM MR_DOCS
LEFT OUTER JOIN MR_USERS
ON USER_FID = USER_PID
LEFT OUTER JOIN MR_DOSSIERS
ON DOSSIER_FID = DOSSIER_PID
LEFT OUTER JOIN MR_NOTES
ON DOC_PID = MR_NOTES.DOC_FID
WHERE BORROW_USER_FID = @UserId
AND DOC_STATE = 5
AND REMINDER_DATE <= getdate()
AND MR_DOCS.isVisible = 1
AND TREE_FID IS NULL
AND TODO_FID IS NULL
-- or borrowed to this user
GROUP BY DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,
requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE
ORDER BY ORDERFIELD DESC

Tim@Allgeier

*** Sent via Developersdex http://www.droptable.com ***

Erland Sommarskog

2005-07-25, 8:29 pm

Jozef de Veuster (nospam@devdex.com) writes:
> Okay, Thanks a lot Erland!
> I couldn't find anything about using CASE .. WHEN in the ORDER BY in the
> Transact SQL, therefore ...

CASE is an expression just like +, SIN() or COLLATE. Thus you can use
it everywhere.

But then there are contexts which has restrictions on whether
you can use expressions. The ORDER BY clause in case of a UNION is
such a case.

Beside the solution you arrived at, you can always use a derived table:

SELECT col1, col2, ...
FROM (SELECT col1, col2, ...
FROM ...
UNION
SELECT col1, col2
FROM ...) AS x
ORDER BY CASE @blah .... END

> CASE @sortField
> WHEN 'DOSSIER_CODE'
> THEN CAST(DOSSIER_CODE AS VARCHAR(50))
> WHEN 'SCAN_DATE'
> THEN CONVERT(VARCHAR, SCAN_DATE, 121)
> ELSE
> CAST(SCAN_DATE AS VARCHAR(50))

So if @sortField is not any of these we sort SCAN_DATE in the
default conversion to Varchar, which has date first?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

--CELKO--

2005-07-26, 7:23 am

This is a violation of SQL-92 and will not port, so either put the sort
column in the SELECT and sort on it , or a comment that you spit on
ANSI/ISO Standards in your code in case someone in the future has to
maintain it.

Damien

2005-07-26, 7:23 am

Hi Jozef,

Minor point - since the two branches of your union look to be
completely distinct, you might want to change from "UNION" to "UNION
ALL" - it may improve performance slightly. On the other hand, I can't
see why you need the union at all. Is it not just a where clause of:

WHERE
((
MR_DOCS.USER_FID = @UserId
AND DOC_STATE IN (1, 3, 4)
)
OR
(
BORROW_USER_FID = @UserId
AND DOC_STATE = 5
))
AND REMINDER_DATE <= getdate()
AND MR_DOCS.isVisible = 1
AND TREE_FID IS NULL
AND TODO_FID IS NULL

Or is there some other difference between the two branches that I
cannot see?

Damien

Jozef de Veuster

2005-07-26, 7:23 am

Hey Damien,

Thanks for your answer. I haven't noticed that I could use an OR instead
of a UNION.
I will try this tomorrow when I'm at my customer again!
(Probably this will help improve the speed, so thank you thank you very
much for this :o))

Greetz,
Tim@Allgeier

*** Sent via Developersdex http://www.droptable.com ***

--CELKO--

2005-07-26, 8:24 pm

Actually, in Standard SQL-92, the ORDER BY has to be a list of column
names that appear in the SELECT clause list -- no computations,
expressions etc. And it is just a good programming practice to pass
the sorting keys to the front end of a tiered architecture.

2005-08-01, 1:23 pm

what you are doing might cause sub-optimal performance.
Should you use

if @blah = 'DOSSIER_CODE'
select ... order by DOSSIER_CODE
else
select ... order by SCAN_DATE
end

the optimizer would know at compile time which column to order on.
As a result, you might get better performance, especially if the tables
are big

Sponsored Links