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
 Site Related Forums
 Article Discussion
 Article: Dynamic ORDER BY
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 4

jpaviles
Starting Member

USA
8 Posts

Posted - 12/16/2002 :  11:45:12  Show Profile  Reply with Quote
Here's my scenario.. The users have the abilitity to pick three fields
to sort the data by.. So I have three variables coming into my stored procedure, but all three can be from a list of six fields..

I can't code the case statements in the order by clause because
it won't let me list the same column names.

I have tried to code the case statements in the select statement
but it doesn't seem to be sorting my data.. I am posting my sql
to see if any one has any ideas..

select tmp2.*,
case @sort1
when 'P' then tmp2.product
when 'A' then tmp2.acctName
when 'H' then tmp2.basehmocd
when 'N' then tmp2.basentwrkid
when 'R' then tmp2.product
when 'T' then tmp2.state
else null
end as orderby1A,
case @sort1
when 'S' then tmp2.pmscore
else null
end as orderby1D,
case @sort2
when 'P' then tmp2.product
when 'A' then tmp2.acctName
when 'H' then tmp2.basehmocd
when 'N' then tmp2.basentwrkid
when 'R' then tmp2.product
when 'T' then tmp2.state
else null
end as orderby2A,
case @sort2
when 'S' then tmp2.pmscore
else null
end as orderby2D,
case @sort3
when 'P' then tmp2.product
when 'A' then tmp2.acctName
when 'H' then tmp2.basehmocd
when 'N' then tmp2.basentwrkid
when 'R' then tmp2.product
when 'T' then tmp2.state
else null
end as orderby3A,
case @sort3
when 'S' then tmp2.pmscore
else null
end as orderby3D
into #temp
from #temp2 tmp2
inner join ##temp1 tmp1 on tmp2.memberid = tmp1.memberid
order by orderby1A asc,
orderby1D desc , orderby2A asc, orderby2D desc, orderby3A asc, orderby3D desc


jpaviles
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 12/16/2002 :  11:49:01  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Quick thought -- you are inserting records into a temp table. This is not the right time to be order'ing! Do it when you SELECT records from the temp table. There is no guarantee the table will store or return records in any particular order unless you sort a SELECT statement.


- Jeff
Go to Top of Page

jpaviles
Starting Member

USA
8 Posts

Posted - 12/16/2002 :  12:02:40  Show Profile  Reply with Quote
The reason I am sorting when I insert into the temp table
is because the next three queries I run from the temp table
don't necessarily have all the fields that are available to sort by..

My next dilema was to figure out how to retain the sort order!!

Even so.. when I do a select on the temp table the data is not sorted.

jpaviles
Go to Top of Page

drifter
Starting Member

USA
8 Posts

Posted - 11/30/2003 :  19:07:26  Show Profile  Visit drifter's Homepage  Reply with Quote
It seems nobody has posted to this thread for nearly a year, but I have found this discussion quite useful. I suspect there is a performance penalty to using this dynamic sort technique, but it definitley makes my code much more maintainable! After tinkering with the suggestions I found in this thread, I finally got my test query working very well with disparate sort field datatypes and selectable sort direction...so I figured I'd share my results with any future visitors trying to decipher this technique. (Hopefully my analysis is correct )

What I found to work best in my situation was to have two input params to my stored proc (both varchar) for the field name to be sorted and the desired sort direction. The guts of the sort are essentially one case statement for each sort direction and field datatype combination followed by the sort direction and separated by a comma. For example:

order by 
	case	--ascending integer fields
		when @SortDir = 'asc' and @SortField = 'IssueID' then b.IssueID
		when @SortDir = 'asc' and @SortField = 'UserID' then a.UserID
		when @SortDir = 'asc' and @SortField = 'CurrentPriorityID' then b.CurrentPriorityID
	end asc,
	case	--ascending date fields
		when @SortDir = 'asc' and @SortField = 'CurrentStatusDate' then b.CurrentStatusDate
		when @SortDir = 'asc' and @SortField = 'Deadline' then b.Deadline
	end asc,
	case	--ascending character fields
		when @SortDir = 'asc' and @SortField = 'Category' then b.Category
		when @SortDir = 'asc' and @SortField = 'CurrentStatus' then b.CurrentStatus
	end asc,
	case	--descending ingeger fields
		when @SortDir = 'desc' and @SortField = 'IssueID' then b.IssueID
		when @SortDir = 'desc' and @SortField = 'UserID' then a.UserID
		when @SortDir = 'desc' and @SortField = 'CurrentPriorityID' then b.CurrentPriorityID
	end desc,
	case	--descending date fields
		when @SortDir = 'desc' and @SortField = 'CurrentStatusDate' then b.CurrentStatusDate
		when @SortDir = 'desc' and @SortField = 'Deadline' then b.Deadline
	end desc,
	case	--descending character fields
		when @SortDir = 'desc' and @SortField = 'Category' then b.Category
		when @SortDir = 'desc' and @SortField = 'CurrentStatus' then b.CurrentStatus
	end desc


I'm no expert, but I think the problem people were having with disparate field data types which required casting them all to varchar in the sort is due to the query engine requiring a single resultant datatype for each case statement. My limited tests indicate that the resultant datatype of the first when..then statement becomes the expected result datatype for the case block. That would certainly explain why breaking out each field datatype into separate case blocks seems to work. I certainly prefer this method over casting every field to varchar because sorting as text is slow and produces incorrect results if you want to sort numbers numerically instead of alphabetically.

Another thing I noticed about the structure of this is that the resulting "order by" block is probably something like:
order by null asc, null asc, Category asc, null desc, null desc, null desc
I come to this conclusion because each case block with no match will return null and each case block always has asc or desc following it. Also, each case block is separated by a comma. Since the sorts are working, it makes sense to me that any null portions of the order by block are essentially ignored by the query engine.

--Drifter

Edited by - drifter on 11/30/2003 19:32:29
Go to Top of Page

jfsanchez2k
Starting Member

1 Posts

Posted - 01/08/2004 :  22:20:20  Show Profile  Reply with Quote
i've been thinking in a simple solution, without modifing your original stored procedure.

Property SortField() As String
Get
Dim o As Object = ViewState("SortField")
If o Is Nothing Then
o = DataGrid1.DataKeyField
End If
Return CStr(o)
End Get
Set(ByVal Value As String)
ViewState("SortField") = Value
End Set
End Property

Sub DataGrid_Sort(ByVal Sender As Object, ByVal e As DataGridSortCommandEventArgs)
DataGrid1.CurrentPageIndex = 0
If SortField = e.SortExpression Then
SortField = e.SortExpression + " DESC"
Else
SortField = e.SortExpression
End If
BindGrid(SortField)
End Sub

Sub BindGrid(ByVal SortFld as String)
Dim ds As New DataSet
ds = GetData()
Dim dv As New DataView(ds.Tables(0))
dv.Sort = SortFld
DataGrid1.DataSource = dv
DataGrid1.DataBind()
End Sub

The sorting is made with the resulting DataSet, using the DataView Sort property, then using that as the Grid.Datasource.
Happy Programming!

Jfsanchez2k
Go to Top of Page

maashu
Starting Member

USA
5 Posts

Posted - 01/14/2004 :  14:45:50  Show Profile  Reply with Quote
Hey All,

First of all, thanks to you Drifter! This was extremely helpful for something I've been scratching my head about. But I'm wondering if anyone knows why the types of fields need to be separated in the CASE statement below. In my testing so far, I've been able to lump the datetime fields in the same CASE statement as the integer fields, but for some reason if I don't put the VARCHAR fields into their own CASE statements it bombs.

Thanks in advance for your input!

quote:
Originally posted by drifter

It seems nobody has posted to this thread for nearly a year, but

<snip>

What I found to work best in my situation was to have two input params to my stored proc (both varchar) for the field name to be sorted and the desired sort direction. The guts of the sort are essentially one case statement for each sort direction and field datatype combination followed by the sort direction and separated by a comma. For example:
[code]
order by
case --ascending integer fields
when @SortDir = 'asc' and @SortField = 'IssueID' then b.IssueID
when @SortDir = 'asc' and @SortField = 'UserID' then a.UserID
when @SortDir = 'asc' and @SortField = 'CurrentPriorityID' then b.CurrentPriorityID
end asc,
case --ascending date fields
when @SortDir = 'asc' and @SortField = 'CurrentStatusDate' then b.CurrentStatusDate
when @SortDir = 'asc' and @SortField = 'Deadline' then b.Deadline
end asc,
case --ascending character fields
when @SortDir = 'asc' and @SortField = 'Category' then b.Category
when @SortDir = 'asc' and @SortField = 'CurrentStatus' then b.CurrentStatus
end asc
<snip>



Cheers,

-maashu
Go to Top of Page

drifter
Starting Member

USA
8 Posts

Posted - 01/14/2004 :  16:17:22  Show Profile  Visit drifter's Homepage  Reply with Quote
maashu, glad I could help

As far as I can determine, the datatype issue with the CASE blocks is a limitation of the CASE statement itself. It seems that the query engine makes an assumption that the return datatype of the CASE block should be the same datatype as whatever the 1st WHEN..THEN statement would return. The end result is that any returned datatype from a WHEN..THEN statement that does not match the datatype of the first WHEN..THEN statement in a CASE block will mismatch the assumed return datatype. My guess is that the datatype assumption is made when the query is compiled, which is obviously before conditions exist to determine the final WHEN..THEN statement to return.

I found, much as you did, that integers and datetime datatypes can coexist in the same case block, and that is probably due to the fact that dates are stored as two 4-byte integers. Since Integers are stored as one 4-byte integer, I am curious if sorting a datetime datatype from a case block when the 1st WHEN..THEN statement is returning an integer would work properly. Specifically, would either half of the datetime be ignored due to the return datatype of the CASE block being integer? If the first half is ignored, dates would not sort properly, but times would. If the second half is ignored, dates would sort properly, but time would not. Alternatively, the query engine might be smart enough to use the largest integer datatype of all the WHEN..THEN statements in the CASE block and assume an 8 byte integer (bigint), which would make using integer and datetime in the same case block a non-issue. If the query engine is not smart enough to do that, simply placing the WHEN..THEN statement that returns a datetime 1st in the CASE block would fix the problem. I wish I had time to try this out today. If you experiment with the sorting issue, let me know what you find!

I haven't found any relevant documentation to support my observations of the case statement return datatype, however it fits observed results under several scenarios that I tried.

I hope that helps
quote:
Originally posted by maashu

Hey All,

First of all, thanks to you Drifter! This was extremely helpful for something I've been scratching my head about. But I'm wondering if anyone knows why the types of fields need to be separated in the CASE statement below. In my testing so far, I've been able to lump the datetime fields in the same CASE statement as the integer fields, but for some reason if I don't put the VARCHAR fields into their own CASE statements it bombs.


--Drifter

Edited by - drifter on 01/14/2004 16:44:19
Go to Top of Page

drifter
Starting Member

USA
8 Posts

Posted - 01/14/2004 :  16:42:03  Show Profile  Visit drifter's Homepage  Reply with Quote
After sending my last response, I decided to actually read the documentation for the CASE statement in "SQL Server Books Online". Believe it or not, I found an explanation for the return datatype mystery for the CASE statement. It's amazing how a little reading can provide answers

According to the documentation, the CASE statement returns the highest precedence type from the set of types in the WHEN..THEN and ELSE expressions. It would stand to reason, then, that as long as all the possible return datatypes of a CASE block will translate to the one with highest precedence it will work fine.

The datatype precedence list for SQL 2000 from highest to lowest is as follows:
  • sql_variant

  • datetime

  • smalldatetime

  • float

  • real

  • decimal

  • money

  • smallmoney

  • bigint

  • int

  • smallint

  • tinyint

  • bit

  • ntext

  • text

  • image

  • timestamp

  • uniqueidentifier

  • nvarchar

  • nchar

  • varchar

  • char

  • varbinary

  • binary

quote:
Originally posted by drifter
I haven't found any relevant documentation to support my observations of the case statement return datatype, however it fits observed results under several scenarios that I tried.


--Drifter

Edited by - drifter on 01/14/2004 16:43:32
Go to Top of Page

maashu
Starting Member

USA
5 Posts

Posted - 01/14/2004 :  16:48:16  Show Profile  Reply with Quote
Good thinking Drifter... as I often do, I overshot the obvious example and dove into "Inside SQL Server 2000," which provided some good information but was nowhere near as detailed as the BOL answer.

I've actually chipped away at most of my problem, but what I need to do now is pass this recordset into a UDF or temp table with an IDENTITY column so I can sequence the records and then pass parameters of the number of records to select from and the starting IDENTITY value to select from.

I've always heard that UDFs are preferable to temp tables, any thoughts on that in this context?

Thanks again!

quote:
Originally posted by drifter

After sending my last response, I decided to actually read the documentation for the CASE statement in "SQL Server Books Online".


Cheers,

-maashu
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36565 Posts

Posted - 01/14/2004 :  16:59:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
I'm confused by your UDF/temp table comment. UDF's can return record sets or a single value. You can pass parameters into them like you would in a stored procedure. You can not pass a recordset into a UDF. The comment that you heard is probably in reference to table variables. These are preferred when the number of rows is small. Perhaps with an example, we can show you what to do.

Tara
Go to Top of Page

maashu
Starting Member

USA
5 Posts

Posted - 01/14/2004 :  17:30:04  Show Profile  Reply with Quote
quote:
Originally posted by tduggan
Perhaps with an example, we can show you what to do.



Hey Tara,

Thanks for the encouragement, I was sort of fishing for an invitation to post my code. I'm new here, just trying to be considerate.

Here's the code I'm working with. It's pretty hairy (at least for me). Basically what it does is allows the user to pass in any number of column names, values and comparison operators, as well as what column to sort by and which way to sort it. Thanks to Drifter, I was able to make quick work of the sorting bit yesterday.

However, now what I need to do is grab only a portion of that record set, in a chunk of a certain number of records. Therefore, I figured the best way to do this was to pass in a "number of records to select" parameter and a "start with record number" parameter.

This is where my question comes in. Is there a way to get the record number from a result set like this with a UDF or would the solution in the first (grey) code block on this page be the best solution?

http://www.4guysfromrolla.com/webtech/062899-1.shtml

Again, thanks for your help!


(code has been slimmed down in the interest of brevity)
----------------------------------------------------------


CREATE PROCEDURE [dbo].spDSN_event_SELECT
@fieldnames nVARCHAR(1000), --string of bell-delimited fieldnames (e.g., "EventID")
@operators VARCHAR(100), --string of bell-delimited operators (e.g., =, <, >)
@values nVARCHAR(1000), --string of bell-delimited values (e.g., 100)
@order_by nVARCHAR(500), --column name to order results by
@asc_desc VARCHAR(4), --sort ascENDing or descENDing
@debug tinyINT = 0 --debug, default is off.
as
---
---
SET NOCOUNT ON
/* Create temp table to populate search conditions */
IF EXISTS (SELECT * FROM tempdb..sysobjects where id = object_id(N'[tempdb].[dbo].[#tmp_conditions]'))
DROP TABLE #tmp_conditions

CREATE TABLE #tmp_conditions (
colName VARCHAR(20) not null,
op VARCHAR(6) not null,
val VARCHAR(80) not null
)
/* populate #tmp_conditions with column names, operators, AND comparison values */
DECLARE @i INT
DECLARE @n INT
DECLARE @tmpCol VARCHAR(128)
DECLARE @tmpOp VARCHAR(128)
DECLARE @tmpVal VARCHAR(128)

EXEC spAC_StrNumTokens @fieldnames, @n OUTPUT

SET @i = 1

WHILE (@i <= @n) BEGIN
EXEC spAC_StrGetToken @fieldnames, @i, @tmpCol OUTPUT
EXEC spAC_StrGetToken @operators, @i, @tmpOp OUTPUT
EXEC spAC_StrGetToken @values, @i, @tmpVal OUTPUT
INSERT INTO #tmp_conditions VALUES (@tmpCol, @tmpOp, @tmpVal)
SET @i = @i + 1
END

DECLARE @proceed TINYINT
SET @proceed = 1
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#tmp_conditions]') )

BEGIN
DECLARE @rc_tmp_cond INT
SELECT @rc_tmp_cond = count(*) FROM #tmp_conditions
END

IF @rc_tmp_cond < 1
SET @proceed = 0
IF @proceed = 0 BEGIN
SELECT * FROM vwEvents
END

ELSE BEGIN

SELECT x.EVENTID, [QUARTER]
FROM (
SELECT EVENTID, [QUARTER]
FROM (
SELECT N.EVENTID, [QUARTER], C.op,
CASE colName
WHEN 'EVENTID' THEN CASE
WHEN op IN ('<','>','=','<>','<=','>=') THEN CASE
WHEN EVENTID < val THEN -1
WHEN EVENTID = val THEN 0
WHEN EVENTID > val THEN 1 END
WHEN op in ('IN','NOT IN') THEN CASE
WHEN charindex(','+cast(EVENTID as VARCHAR(10))+',',','+replace(val,space(1),space(0))+',') > 0 THEN 1
ELSE 0 END
WHEN op IN ('IS NULL','IS NOT NULL') THEN CASE
WHEN EVENTID IS NULL THEN 1
WHEN EVENTID is not null THEN 0 END
END
WHEN '[QUARTER]' THEN CASE
WHEN op = 'LIKE' THEN CASE
WHEN [QUARTER] LIKE val THEN 1 END
WHEN op IN ('=','<>') THEN CASE
WHEN [QUARTER] = val THEN 0
WHEN [QUARTER] IS NULL THEN 1
WHEN [QUARTER] <> val THEN 1 END
WHEN op IN ('IN','NOT IN') THEN CASE
WHEN charindex(','+ [QUARTER] +',',','+replace(val,','+space(1), ',') +',') > 0 THEN 1 END
WHEN op IN ('=','<>') THEN CASE
WHEN [QUARTER] = val THEN 0
WHEN [QUARTER] IS NULL THEN 1
WHEN [QUARTER] <> val THEN 1 END
END
END AS compared
FROM vwEvents N, #tmp_conditions C
) T
GROUP BY EVENTID, [QUARTER]
HAVING MIN (
CASE
WHEN compared IS NULL THEN 0
WHEN op = '<' THEN CASE WHEN compared >= 0 THEN 0 ELSE 1 END
WHEN op = '>' THEN CASE WHEN compared <= 0 THEN 0 ELSE 1 END
WHEN op = '<=' THEN CASE WHEN compared > 0 THEN 0 ELSE 1 END
WHEN op = '>=' THEN CASE WHEN compared < 0 THEN 0 ELSE 1 END
WHEN op = '=' THEN CASE WHEN compared <> 0 THEN 0 ELSE 1 END
WHEN op = '<>' THEN CASE WHEN compared = 0 THEN 0 ELSE 1 END
WHEN op = 'LIKE' THEN CASE WHEN compared <> 1 THEN 0 ELSE 1 END
WHEN op = 'IN' THEN CASE WHEN compared <> 1 THEN 0 ELSE 1 END
WHEN op = 'NOT IN' THEN CASE WHEN compared = 1 THEN 0 ELSE 1 END
WHEN op = 'IS NULL' THEN CASE WHEN compared <> 1 THEN 0 ELSE 1 END
WHEN op = 'IS NOT NULL' THEN CASE WHEN compared = 1 THEN 0 ELSE 1 END
END
) = 1
) x

ORDER BY CASE
--ascENDing fields (non-string)
WHEN @asc_desc = 'ASC' AND @order_by = 'EventID' THEN x.EventID
END
ASC, CASE
--ascENDing fields (string)
WHEN @asc_desc = 'ASC' AND @order_by = 'Quarter' THEN x.[Quarter]
END
ASC,

CASE
--descENDing fields (non-string)
WHEN @asc_desc = 'DESC' AND @order_by = 'EventID' THEN x.EventID
END
DESC,

CASE --descENDing fields (string)
WHEN @asc_desc = 'DESC' AND @order_by = 'Quarter' THEN x.[Quarter]
END
DESC

END


----------------------------------------------------------

Cheers,

-maashu
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36565 Posts

Posted - 01/14/2004 :  17:41:23  Show Profile  Visit tkizer's Homepage  Reply with Quote
I would start a new thread for your question as this one might not get much attention in this thread. But yes the paging solution that is listed in that link is commonly used. Jeff Smith (jsmith8858) has a paging solution that does not use temp tables, so his probably faster as long as you are able to implement it. I would search the SQLTeam forums for his solution if he doesn't post it here (if he sees the thread, I'm sure he'll show you how to do it). I have not seen any solutions that use UDFs.

Tara
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36565 Posts

Posted - 01/14/2004 :  17:45:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
Here's Jeff's solution:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27297

Tara
Go to Top of Page

maashu
Starting Member

USA
5 Posts

Posted - 01/14/2004 :  17:49:40  Show Profile  Reply with Quote
quote:
Originally posted by tduggan
Jeff Smith (jsmith8858) has a paging solution that does not use temp tables



Hey Tara,

For anyone who's following this thread, here's a link to Jeff's solution:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25496

The only thing is that this method only works if your query is "already ordered by a unique field," and I can't guarantee that for this one.

I think the solution on 4guysfromrolla is going to be my best bet at this point. I really appreciate your feedback and suggestions!



Cheers,

-maashu
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/14/2004 :  18:21:50  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
there's a variation my web blogs -- check out the sqlteam weblogs.

you can order by more than 1 column, but each row must be uniquely identifiable by the columns which you are ordering by. which makes sense in a RDMS kind of way.

the biggest cavaet with my technique is that the ordering cannot be dynamic -- you must hard code it in.

http://weblogs.sqlteam.com/jeffs/

- Jeff
Go to Top of Page

drifter
Starting Member

USA
8 Posts

Posted - 01/14/2004 :  18:39:33  Show Profile  Visit drifter's Homepage  Reply with Quote
I have a stored proc that I created which uses both dynamic filtering and dynamic sorting. Since so much of the procedure is dynamic, I use a table datatype with an identity field to receive the results of the query and then send the desired page of results by setting the rowcount to my desired page size and selecting from the table variable where the identity is >= the desired starting row. While this isn't nearly as efficient as Jeff's approach, I'm limited due to the dynamic filtering and dynamic sorting. The technique I use works quite well with reasonably sized source tables though. If anyone is interested in the details of what I did, let me know with a direct e-mail.

--Drifter
Go to Top of Page

maashu
Starting Member

USA
5 Posts

Posted - 01/14/2004 :  19:28:09  Show Profile  Reply with Quote
quote:
Originally posted by jsmith8858

you can order by more than 1 column, but each row must be uniquely identifiable by the columns which you are ordering by. which makes sense in a RDMS kind of way.



Hey Jeff,

Thanks for your input... I actually realized that after I'd sent my previous post, but the problem with my stuff is that the queries include multiple tables but not all of the PKs are included in the queries. Therefore, there's no way to guarantee that every record in every query I'm going to apply this to will be unique, even when ordering on all the columns.

The approach with the temp table from 4guysfromrolla is still looking promising, but I'm going to ask drifter about his table datatype solution.

Thanks again for your help!


Cheers,

-maashu
Go to Top of Page

pnunbe
Starting Member

Italy
3 Posts

Posted - 09/04/2004 :  06:47:42  Show Profile  Reply with Quote
quote:
Originally posted by Wildthing

To get around the datatype problem, use seperate group CASE statements for each datatype.

ORDER BY
CASE WHEN @SORTBY = 1 THEN Numeric1
WHEN @SORTBY = 2 THEN Numeric2
END,
CASE WHEN @SORTBY = 3 THEN VarChar1
WHEN @SORTBY = 4 THEN VarChar2
END DESC,
CASE WHEN @SORTBY = 5 THEN DateTime1
END

Note that each case block is seperated by a comma (after END) except the last block. Also note that you can use ASC or DESC for each CASE statement group.




Hi,
how does this work?
Looks like it should expand (for @SORTBY=5) to:
ORDER BY ,,DateTime1
which is obviously wrong.
Thanks

Spaghetti software is my meatball!
Go to Top of Page

drifter
Starting Member

USA
8 Posts

Posted - 09/04/2004 :  10:59:02  Show Profile  Visit drifter's Homepage  Reply with Quote
quote:
Originally posted by pnunbe

quote:
Originally posted by Wildthing

To get around the datatype problem, use seperate group CASE statements for each datatype.

ORDER BY
CASE WHEN @SORTBY = 1 THEN Numeric1
WHEN @SORTBY = 2 THEN Numeric2
END,
CASE WHEN @SORTBY = 3 THEN VarChar1
WHEN @SORTBY = 4 THEN VarChar2
END DESC,
CASE WHEN @SORTBY = 5 THEN DateTime1
END

Note that each case block is seperated by a comma (after END) except the last block. Also note that you can use ASC or DESC for each CASE statement group.




Hi,
how does this work?
Looks like it should expand (for @SORTBY=5) to:
ORDER BY ,,DateTime1
which is obviously wrong.
Thanks

Spaghetti software is my meatball!



The resulting "order by" block is probably something like:

order by null, null desc, DateTime1

I come to this conclusion because each case block with no match will return null and each case block always has asc or desc following it. (asc is assumed if sort direction isn't specified) Also, each case block is separated by a comma. Since the sorts are working, it makes sense to me that any null portions of the order by block are essentially ignored by the query engine.



--Drifter

Edited by - drifter on 09/04/2004 11:04:18
Go to Top of Page

pnunbe
Starting Member

Italy
3 Posts

Posted - 09/05/2004 :  04:46:18  Show Profile  Reply with Quote
Hi,
your guess about the "order by" block expansion is surely better than mine; yet if I submit to QueryAnalizer a query containing a clause like:
order by null,null, ...
I get an error message stating that the same column appears multiple times in the "order by" clause.
So I am not yet sure about how your method works.

Spaghetti software is my meatball!
Go to Top of Page
Page: of 4 Previous Topic Topic Next Topic  
Previous Page | Next Page
 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.14 seconds. Powered By: Snitz Forums 2000