Using Case in Order By Clause & Order by @Arg

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

SQL Server Development (2000)

Using Case in Order By Clause & Order by @Arg

Author

Topic

HarryMann
Starting Member

33 Posts

Posted - 2002-03-19 : 17:27:58

I love the power of Case statements and whilst in the past have found syntactical wrinkles have usually found a way through the (very badly documented) variety of constructs.

Now, there are 2 interrelated problems:

1) Sorting a resultset based upon an input parameter will always give an annoying (& I'm convinced parsing bug) parsetime message that one 'cannot use a numeric col posn argument' to order a resultset, EVEN when I declare my input arg as varchar and pass in the actual column name.

2) To avoid this (bug, feature or over enthusiastic parser) I have previously used a Case statement in the Order By clause. It has worked, passing in a column number or name:

However, now, with the exact same syntax, I can get it to parse and compile BUT when run, I get a silly error (unless the sort column is of a numeric data type) which error in Query Analyser says that cannot coerce a varchar to a float or int, Line Number:= the line number of the first Select statement line, which has nothing at all to do with the problem, since removing the Order By clauses removes this error.

The TSQL Stored proc is: (and this construct works fine elsewhere, is actually in production):

CREATE PROCEDURE sp_ETD_ViewMarketPlace_OTV
@intOTV int

AS

Select sd.DSASubDeclarationNo As 'DSA Ref.',
sd.DecQuantity as 'Qty.',
c.CommodityName as 'Cmdty.',
cc.CommodityClassName as 'Cmdty. Class',
dt.DisposalMethodName as 'Disp. Method'

from tSubDeclaration sd

Inner Join mtQuantityType q on sd.QuantityTypeID = q.QuantityTypeID
Inner Join tCommodity c On sd.CommodityID = c.CommodityID
Inner Join tCommodityClass cc On c.CommodityClassID = cc.CommodityClassID
Inner Join mtDisposalMethod dt On sd.DisposalMethodID = dt.DisposalMethodID

Where IsDate(sd.Whensold) = 0 and sd.DSASubDeclarationNo Like '200%'

Order By Case
When (1=@intOTV) Then sd.DSASubDeclarationNo
When (2=@intOTV) Then sd.DecQuantity
When (3=@intOTV) Then c.CommodityName
When (4=@intOTV) Then cc.CommodityClassName
When (5=@intOTV) Then dt.DisposalMethodName
Else sd.DSASubDeclarationNo
END

--This Order By Case
When Condition Then OrderBy ColumnName

DOES work, but why not here

Any help or suggestions very gratefully received - Thanks

HarryMann
Starting Member

33 Posts

Posted - 2002-03-19 : 20:24:33

Clarification:

That all sounded a bit confused! Simply put, Garth illustrates an example of using 'Dynamic Order By' on 1/22/2001 on this site.

It works, but he only sorts on numeric fields, whilst my query attempts to sort dynamically (using an Onput argument) on varchar fields. I get 'Cannot convert varchar to float' line xxxx where linexxx i the first line of the Select Statement.

This is crazy, as I have another sp running which does this identically and it does sort dynamically and correctly, but on a different table.

Any help out there, can I msg Garth in person?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-19 : 20:28:35

In order for the parser to process the ORDER BY correctly, all of the CASE expressions must have the same datatype. If you mix numeric columns with char/varchar columns, you must convert the numeric values to char or varchar in the ORDER BY clause. This also applies to datetime values.

HarryMann
Starting Member

33 Posts

Posted - 2002-03-19 : 20:50:00

Thanks RobVolk,

Really nice to have you folks out there, listening out, fraid I can't help out with many at the moment, but learning all the time .

A bit of a restriction but spose I can see why this is; that'll be it, as theres just ONE numeric column, a float - Mmmmm what a nuisance, so how else can we sort dynamically?

CSmith

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-19 : 20:59:22

It's no big deal to change the CASE statement a little:

Order By Case
When (1=@intOTV) Then CONVERT(varchar(10), sd.DSASubDeclarationNo)
When (2=@intOTV) Then CONVERT(varchar(10), sd.DecQuantity)
...

I think the STR() function might be better than CONVERT() for number conversion though, because it allows for more precise control over the value returned. FYI

I usually move the CASE expression into the SELECT list, then give that column an alias. You can then ORDER BY the alias for the column, not the CASE expression:

Select sd.DSASubDeclarationNo As 'DSA Ref.',
sd.DecQuantity as 'Qty.',
c.CommodityName as 'Cmdty.',
cc.CommodityClassName as 'Cmdty. Class',
dt.DisposalMethodName as 'Disp. Method',
Case
When (1=@intOTV) Then sd.DSASubDeclarationNo
When (2=@intOTV) Then sd.DecQuantity
When (3=@intOTV) Then c.CommodityName
When (4=@intOTV) Then cc.CommodityClassName
When (5=@intOTV) Then dt.DisposalMethodName
Else sd.DSASubDeclarationNo
END AS SortCol

from tSubDeclaration sd

Inner Join mtQuantityType q on sd.QuantityTypeID = q.QuantityTypeID
Inner Join tCommodity c On sd.CommodityID = c.CommodityID
Inner Join tCommodityClass cc On c.CommodityClassID = cc.CommodityClassID
Inner Join mtDisposalMethod dt On sd.DisposalMethodID = dt.DisposalMethodID

Where IsDate(sd.Whensold) = 0 and sd.DSASubDeclarationNo Like '200%'

Order By SortCol

The only problem is that you have an extra column outputted in the SELECT list. It's a trade-off.

The only way to completely avoid these workarounds, without using dynamic SQL, is to create an IF...THEN block for each ORDER BY option, then put the entire SQL statement with the proper ORDER BY within each block. Yeah, it duplicates a lot of code, but it also optimizes perfectly for each ORDER BY condition, which none of these other methods can do.

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-03-19 : 22:42:14

I am not 100% sure on this and maybe someone else here can shed some further light on this, but I think you may be getting the conversion error because in your case statement, when you compare:
When (1=@intOTV) Then sd.DSASubDeclarationNo
When (2=@intOTV) Then sd.DecQuantity
When (3=@intOTV) Then c.CommodityName
When (4=@intOTV) Then cc.CommodityClassName
When (5=@intOTV) Then dt.DisposalMethodName

I was under the assumption that the 1 in the select statement would refer to the column for comparison. In other words, SQL interprets this statement as the following:

When (sd.DSASubDeclarationNo=@intOTV) Then sd.DSASubDeclarationNo
When (sd.DecQuantity=@intOTV) Then sd.DecQuantity
When (c.CommodityName=@intOTV) Then c.CommodityName
When (cc.CommodityClassName=@intOTV) Then cc.CommodityClassName
When (dt.DisposalMethodName=@intOTV) Then dt.DisposalMethodName

If this truly is the case, I would think that you could change you Case statement to the following:

When (@intOTV=1) Then sd.DSASubDeclarationNo
When (@intOTV=2) Then sd.DecQuantity
When (@intOTV=3) Then c.CommodityName
When (@intOTV=4) Then cc.CommodityClassName
When (@intOTV=5) Then dt.DisposalMethodName

I hope this helps or that someone can confirm what I think is correct.

Keep me posted on if this solves the problem.

Jeremy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources