Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Order By with Case statement Expand / Collapse
Author
Message
Posted Wednesday, June 3, 2009 10:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
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?
Post #728384
Posted Wednesday, June 3, 2009 12:56 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
What is wrong?
Post #728463
Posted Wednesday, June 3, 2009 1:12 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 4,013, Visits: 6,098
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? -- Stephen Stills
Post #728471
Posted Wednesday, June 3, 2009 1:24 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Hi Bob,
Converting the results to VarChar did fix it. Thanks for your help.
Post #728481
Posted Wednesday, June 3, 2009 6:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 8:11 PM
Points: 6,604, Visits: 8,910
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
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
Post #728607
Posted Friday, June 5, 2009 8:44 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 4,013, Visits: 6,098
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? -- Stephen Stills
Post #729792
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse