Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Order By with Case statement


Order By with Case statement

Author
Message
ramadesai108
ramadesai108
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 690
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?
ramadesai108
ramadesai108
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 690
What is wrong?
The Dixie Flatline
The Dixie Flatline
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3398 Visits: 6899
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
ramadesai108
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 690
Hi Bob,
Converting the results to VarChar did fix it. Thanks for your help.
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6235 Visits: 10403
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

The Dixie Flatline
The Dixie Flatline
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3398 Visits: 6899
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search