Problems with CASE statement
-
Banging my head against the wall with this simple SQL statement.
SELECT
locationId, locationCode, description, divisionCode, companyId
FROM locations
ORDER BY companyId,
CASE
WHEN ISNUMERIC(locationCode) = 0 THEN locationCode
WHEN ISNUMERIC(locationCode) = 1 THEN CAST(locationCode AS INT)
END
I get the error Conversion failed when converting the varchar value 'ADMN' to data type int. If I do a SELECT ISNUMERIC(locationCode) it correctly returns 0 for that row. So why is SQL trying to convert this value?
I'm on SQL server 2005.
Any help is appreciated.
-
The CASE expression doesn't want to return an integer in some rows and a varchar in other rows.
So, what are you trying to accomplish by converting the locationCode to an Int? Give us some sample data and how you want it sorted.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Ahh crap. I see now. Not enough sleep last night.
Sample data:
Location Code Description Division Code Company Code 6 DESC AA 0000 67 DESC AA 0000 7 DESC AA 0000 78 DESC AA 0000 79 DESC AA 0000 8 DESC AA 0000 82 DESC AA 0000 88 DESC AA 0000 89 DESC AA 0000 ADMN DESC AA 0000 Not sure why it makes a huge space above the table ... but there it is.
As you can see, it sorts numbers like 6, 67, 7, 78, 79. Which is incorrect.
I need it to be sorted like 6, 7, 67, 78, 79.
-
Try this in your ORDER BY clause:
CASE
WHEN
ISNUMERIC(locationCode) = 0 THEN locationCode
WHEN
ISNUMERIC(locationCode) = 1 THEN '0000000' + Locationcode
END
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
That didn't exactly work out like I needed it to. But you gave me an idea. I really appreciate the help. Thank you.
Here's what I ended up using
SELECT
locationId, locationId AS original_locationId, locationCode, description, divisionCode, companyId
FROM locations
ORDER BY companyId,
CASE
WHEN ISNUMERIC(locationCode) = 0 THEN '9999'
WHEN ISNUMERIC(locationCode) = 1 THEN CAST(locationCode AS INT)
END,
locationCode
-
ISNUMERIC does NOT mean IS ALL DIGITS... you're making a BIG mistake using it for that. If you don't think so, try this...
SELECT NULL AS [ASCII#],'12D45' AS [Character(s)],ISNUMERIC('12D45') AS [ISNUMERIC Returns] UNION ALL
SELECT NULL,'12E45',ISNUMERIC('12E45') UNION ALL
SELECT NULL,'$1,000',ISNUMERIC('$1,000') UNION ALL
SELECT NULL,'1,000',ISNUMERIC('1,000') UNION ALL
SELECT NULL,'1000.0',ISNUMERIC('1000.0') UNION ALL
SELECT NULL,'-1000.0',ISNUMERIC('-1000.0') UNION ALL
SELECT NULL,'+1000.0',ISNUMERIC('+1000.0') UNION ALL
SELECT Number,CHAR(Number),ISNUMERIC(CHAR(Number))
FROM Master.dbo.spt_Values
WHERE NAME IS NULL
AND ISNUMERIC(CHAR(Number)) = 1
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Try something like this, instead...
SELECT
locationId, locationId AS original_locationId, locationCode, description, divisionCode, companyId
FROM locations
ORDER BY companyId,
CASE
WHEN locationCode NOT LIKE '%[^0-9]%' THEN STR(locationcode,10)
ELSE locationcode
END,
locationCode
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Another idea that might work for you is:
ORDER BY companyId, isnumeric(locationCode), len(locationCode), locationCode
Steve
-
There's that "ISNUMERIC" thing again... see a couple of frames up for the warning
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply