473,740 Members | 7,355 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Using CASE .. WHEN to have 'dynamic' sort

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_FI D = USER_PID
LEFT OUTER JOIN MR_DOSSIERS
ON DOSSIER_FID = DOSSIER_PID
LEFT OUTER JOIN MR_NOTES
ON DOC_PID = MR_NOTES.DOC_FI D
WHERE MR_DOCS.USER_FI D = 1
AND DOC_STATE IN (1, 3, 4)
AND REMINDER_DATE <= getdate()
AND MR_DOCS.isVisib le = 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_FI D
WHERE BORROW_USER_FID = 1
AND DOC_STATE = 5
AND REMINDER_DATE <= getdate()
AND MR_DOCS.isVisib le = 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.developersdex.com ***
Jul 25 '05 #1
10 3009
Jozef de Veuster (no****@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, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 25 '05 #2
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.developersdex.com ***
Jul 25 '05 #3
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.developersdex.com ***
Jul 25 '05 #4
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_CO DE 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_FI D = USER_PID
LEFT OUTER JOIN MR_DOSSIERS
ON DOSSIER_FID = DOSSIER_PID
LEFT OUTER JOIN MR_NOTES
ON DOC_PID = MR_NOTES.DOC_FI D
WHERE MR_DOCS.USER_FI D = @UserId
AND DOC_STATE IN (1, 3, 4)
AND REMINDER_DATE <= getdate()
AND MR_DOCS.isVisib le = 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_CO DE 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_FI D
WHERE BORROW_USER_FID = @UserId
AND DOC_STATE = 5
AND REMINDER_DATE <= getdate()
AND MR_DOCS.isVisib le = 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.developersdex.com ***
Jul 25 '05 #5
Jozef de Veuster (no****@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_CO DE 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, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 25 '05 #6
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.

Jul 26 '05 #7
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_FI D = @UserId
AND DOC_STATE IN (1, 3, 4)
)
OR
(
BORROW_USER_FID = @UserId
AND DOC_STATE = 5
))
AND REMINDER_DATE <= getdate()
AND MR_DOCS.isVisib le = 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

Jul 26 '05 #8
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.developersdex.com ***
Jul 26 '05 #9
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.

Jul 26 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
10237
by: Alex Vorobiev | last post by:
hi there, i am using sql server 7. below is the stored procedure that is giving me grief. its purpose it two-fold, depending on how it is called: either to return a pageset (based on page number and page size), or to return IDs of previous and next records (based on current record id). the problem is, that the order in which records are inserted into the temp table is inconsistent, even though the calling statement and the order by is...
19
7286
by: Lauren Quantrell | last post by:
I have a stored procedure using Convert where the exact same Convert string works in the SELECT portion of the procedure but fails in the WHERE portion. The entire SP is listed below. Specifically, I have a problem with this portion in the WHERE clause: DATEADD(Day,tblMyEventTableName.ReminderDays, @DateNow) Between CONVERT(smalldatetime,str(DATEPART(Month, @DateNow)+1) + '/' + str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/'...
40
4316
by: Elijah Bailey | last post by:
I want to sort a set of records using STL's sort() function, but dont see an easy way to do it. I have a char *data; which has size mn bytes where m is size of the record and n is the number of records. Both these numbers are known
2
8496
by: Ken Tucker | last post by:
I've read about this issue in many articles across the net... But haven't found a solution. I see all kinds of custom code to perform sorting with datagrids, but my example is so simple, I must just be missing something. Basically, I have a .aspx page that is very simple and has a simple datagrid. All code is in the .cs page for the datagrid (hence, I'm doing all datagrid work programmatically). The datagrid is populated from a SQL table...
4
1234
by: Roy | last post by:
Greetings, I've been avoiding it for so long, but like an evil wraith it always returns to haunt me. The bane of my existence, it is... bidirectional sorting!!! Checked out previous posts and none seem to be of assistance to me. Here's the scoop. I have a web app which populates a sortable, pageable datagrid using a stored proc. Everything works great, however, I've
0
3390
by: anonieko | last post by:
This approach I found very efficient and FAST when compared to the rowcount, or Subquery Approaches. This is before the advent of a ranking function from DB such as ROW_NUMBER() in SQL Server 2005 and the likes of it. So This one works with SQL2000 What do you think?
6
7681
by: smmk25 | last post by:
Before I state the problem, I just want to let the readers know, I am knew to C++\CLI and interop so please forgive any newbie questions. I have a huge C library which I want to be able to use in a .NET application and thus am looking into writing a managed C++ wrapper for in vs2005. Furthermore, this library has many callback hooks which need to be implemented by the C++ wrapper. These callback functions are declared as "extern C...
10
9217
by: shubha.sunkada | last post by:
Hi, I have a recordset connection in asp that I am using to search records.If I use the client side cursorlocation (rs.cursorlocation=3) then it takes really long to return back the records due to which a timeout occurs.If I change the cursorlocation to adUseNone(1) or adUseServer(2) then the search is faster and without any problems.But the sort on records cannot be done if I use adUseClient(3).I need to have sort on these records.
18
2294
by: Angus | last post by:
Hello We have a lot of C++ code. And we need to now create a library which can be used from C and C++. Given that we have a lot of C++ code using classes how can we 'hide' the fact that it is C++ from C compilers? Can we have a C header file which uses the functionality of the C++ files and compile this into a lib file?
0
8969
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8794
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9484
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9211
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6756
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6056
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4826
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2748
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2195
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.