Order By Case Confusion

I can't seem to figure out what is going on here... try this code. It should return 3 rows with no problem as is.

===========
DECLARE
@.SortOn varchar(20)

SET @.SortOn = 'dicount'

SELECT discounttype,
isNull(stor_id,'') stor_id,
isNull(lowqty,0) lowqty,
isNull(highqty,0) highqty,
discount
FROM pubs..discounts
ORDER BY CASE @.SortOn
WHEN 'discounttype' THEN discounttype
WHEN 'stor_id' THEN stor_id
WHEN 'lowqty' THEN lowqty
WHEN 'highqty' THEN highqty
WHEN 'dicount' THEN discount
END
===========

Now set @.SortOn = 'discounttype' and you get:
Server: Msg 8114, Level 16, State 5, Line 5
Error converting data type varchar to numeric.

?

Now for more confusion.. when you comment out lines 3,4,and 5 of the CASE statement (WHEN 'lowqty'...'highqty'...'dicount' lines) you once again get 3 rows returned without error.

Anyone able to shed some light on what is goin on?

[MESSAGE EDITED: misspelled discounttype.. sorry]The problem you are having is that you have numeric types mixed in with a varchar data type in your order by clause. SQL is expecting a numeric - look at data type precedence.|||Try this:

DECLARE @.SortOn varchar(20)

SET @.SortOn = 'dicount'

SELECT discounttype,
isNull(stor_id,'') stor_id,
isNull(lowqty,0) lowqty,
isNull(highqty,0) highqty,
discount
FROM pubs..discounts
ORDER BY
CASE @.SortOn
WHEN 'discounttype' THEN discounttype
WHEN 'stor_id' THEN CAST(stor_id AS VARCHAR)
WHEN 'lowqty' THEN CAST(lowqty AS VARCHAR)
WHEN 'highqty' THEN CAST(highqty AS VARCHAR)
WHEN 'dicount' THEN CAST(discount AS VARCHAR)
END|||The only problem with that code is that it will sort the numerics as varchars(ascii character comparision) and not as a true numeric. So values 10,9,12 would be sorted as 10,12,9 (ascending) and 9,12,10 (descending).|||The problem you are having is that you have numeric types mixed in with a varchar data type in your order by clause i've looked at that answer several times, and the error message too, and i still don't understand why it would throw an error

but then, the query's kind of weird too

why not stick the whatever-i-want-to-sort-on-this-week into an extra column in the result set and sort on that...SELECT discounttype
, isNull(stor_id,'') stor_id
, isNull(lowqty,0) lowqty
, isNull(highqty,0) highqty
, discount
, CASE @.SortOn
WHEN 'discounttype' THEN discounttype
WHEN 'stor_id' THEN stor_id
WHEN 'lowqty' THEN lowqty
WHEN 'highqty' THEN highqty
WHEN 'dicount' THEN discount
END as sortfield
FROM pubs..discounts
ORDER BY sortfieldmaybe that'll throw the same error, i dunno

rudy
http://rudy.ca/|||Originally posted by r937
maybe that'll throw the same error, i dunno

Nice thought but that errors as well.

I have solved my problem by seperating each datatype with an IF clause like so:

IF @.SortOn in (..) BEGIN -- varchars
SELECT ...
FROM ..
ORDER BY CASE @.SortOn
WHEN (varchars... etc..)
END
END ELSE BEGIN -- numerics
SELECT ...
FROM ..
ORDER BY CASE @.SortOn
WHEN (numerics... etc..)
END
END

Kinda messy but it works. Any thoughts to make it a bit more graceful would be appreciated.

Thanks for the replies!