June 3, 2009 at 10:59 am
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?
June 3, 2009 at 12:56 pm
What is wrong?
June 3, 2009 at 1:12 pm
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
June 3, 2009 at 1:24 pm
Hi Bob,
Converting the results to VarChar did fix it. Thanks for your help.
June 3, 2009 at 6:17 pm
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
June 5, 2009 at 8:44 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply