Order By with Case statement

Question

Post reply

Order By with Case statement

ramadesai108

SSChampion

Points: 10609
More actions
June 3, 2009 at 10:59 am

#138363

Hi All,
I am running the following SQL statement with Case statement in Order by clause:
SELECT ssID
FROM TblA
ORDER BY
CASE WHEN CHARINDEX('-',ssID) = 0 THEN --there are no dashes
ssID
ELSE
CASE WHEN ISNUMERIC(SUBSTRING(ssID,CHARINDEX('-',ssID) + 1,LEN(ssID) - CHARINDEX('-',ssID))) = 1 THEN
CONVERT(INT,SUBSTRING(ssID,CHARINDEX('-',ssID) + 1,LEN(ssID) - CHARINDEX('-',ssID)))
ELSE --there are dashes but no numbers after dashes
ssID
END
END
Here is the data:
AB26-1
AB26-2
AB26-3
AB26-4
AB26-5
AB26
Running the query error out with:
Conversion failed when converting the nvarchar value 'AB26' to data type int
It seems to be erroring on the last line of data, "AB26", which does not have a dash.
Since it does not have a dash, it should not even go thru the 2nd case statement.
What is wrong here?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ramadesai108 SSChampion Points: 10609 More actions · Answer 1

ramadesai108

SSChampion

Points: 10609

June 3, 2009 at 12:56 pm

#1004238

What is wrong?

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

The ORDER BY wants all values to be of the same datatype. Where you find a '-' in the string for ssid, such that a numeric value is available, you are returning that numeric value as an integer. Where you don't find a '-', you are returning the entire string 'ABC26'. You either need to CONVERT the integer results to varchar, or arbitrarily return a 0 or null instead of ssid, where there is no '-##' in the string.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

ramadesai108 SSChampion Points: 10609 More actions · Answer 3

Hi Bob,

Converting the results to VarChar did fix it. Thanks for your help.

WayneS SSC Guru Points: 95409 More actions · Answer 4

ramadesai108 (6/3/2009)
Hi Bob,
Converting the results to VarChar did fix it. Thanks for your help.

Now you are doing a lot of extra work. After your IsNumeric check, just return the value instead of converting it to an int. When the IsNumeric returns 0, and you are returning a zero, return a '0' instead.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

I suggested that as an alternative. I assume that in his case, he wants to know the value that doesn't have a dash in it.

But, if that's the case, it's probably more efficient to just return the the string to the right of the dash WITHOUT converting it to an integer.

And then there's the question of ordering in this sequence 1,2,11 (integer) or 1,11,2 (char).

Fun, fun, fun. 🙂

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills