Conversion Error When Using CASE Statement

Question

Post reply

Conversion Error When Using CASE Statement

Polymorphist

SSCommitted

Points: 1909
More actions
February 1, 2020 at 12:51 am

#3722188
Hello All -
I feel like a dope here, but I need some help. I have a case statement which is throwing a conversion error and i'm trying to figure this out. All fields involved in the CASE are all numeric (int), I check them with the ISNUMERIC() function. However, when run the following statement I get the corresponding error. When I cast everything to VARCHAR(), the AGE >= THRESHOLD comparison doesn't work correctly.
any ideas?
```
SELECT CASE WHEN PROCSTATUS_NUM = 1 AND AGE >= THRESHOLD THEN 0 ELSE 4 END as Sort
```
ERROR:
Msg 245, Level 16, State 1, Line 11
Conversion failed when converting the ****** value '******' to data type ******.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

Maybe the obscured error message contains some clues

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

From the limited information you have provided in your question and the error message I would guess that column PROCSTATUS_NUM is not a numeric data type (maybe it's character?) and contains a value that cannot be converted to a number (maybe alphabetic or punctuation?).

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

Polymorphist wrote:

When I cast everything to VARCHAR(), the AGE >= THRESHOLD comparison doesn't work correctly.

you need to give us a bit more - but if you are casting everything to varchar then you will get isssues - for example you would expect 21 to come before 112 - but since it's a char then 112 comes first

at least give us the value types it is trying to convert from and to (in your error message). -i'm pretty sure that bigint, float, nvarchar etc are not confidential as part of a script

MVDBA

Polymorphist SSCommitted Points: 1909 More actions · Answer 4

Thanks for the replies. I have to apologize, this query is from an OLTP system behind a very large healthcare application, it's very messy. I essentially got lost inside the code and didn't realize that there was another field called THRESHOLD which had an IIF string formatted for use inside Excel, all I had to do was remove that.

I'm trying to move this code out of a VBScript file and into SSRS, and though I love the challenge, the code here is....ugh. I'm gonna make it better, though, i'm gonna make it better....

Thanks for the responses 🙂

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 5

ISNUMERIC() doesn't do what you think it does. I think you'd be surprised by what all SQL Server considers "numeric".

SELECT 
cv.char_val,
is_numeric = ISNUMERIC(cv.char_val),
is_int = TRY_CONVERT(INT, cv.char_val),
is_decimal = TRY_CONVERT(DECIMAL(9, 2), cv.char_val),
is_float = TRY_CONVERT(FLOAT, cv.char_val)
FROM
( VALUES ('12345'), ('12345.678'), ('$100.00'), ('12345.54321E54'), ('5E+4'), ('123,456,90.0'), ('123.456.78,12') ) cv (char_val);

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

Jason A. Long wrote:

ISNUMERIC() doesn't do what you think it does. I think you'd be surprised by what all SQL Server considers "numeric".

SELECT 
cv.char_val,
is_numeric = ISNUMERIC(cv.char_val),
is_int = TRY_CONVERT(INT, cv.char_val),
is_decimal = TRY_CONVERT(DECIMAL(9, 2), cv.char_val),
is_float = TRY_CONVERT(FLOAT, cv.char_val)
FROM
( VALUES ('12345'), ('12345.678'), ('$100.00'), ('12345.54321E54'), ('5E+4'), ('123,456,90.0'), ('123.456.78,12') ) cv (char_val);

You should add the money data type to this - it shows that ISNUMERIC recognizes money formats and will convert those correctly.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

It'll also say that ',,,,,,,,,,,' is a valid numeric. And the cool part about converting to MONEY during imports (if you don't need more than 4 decimal places) is that it will drop most of the "white space control characters" such as TAB, Cr, Lf, FF, Vt, and even "hard spaces" from after the number being converted.

--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)

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 8

Jeffrey Williams wrote:

You should add the money data type to this - it shows that ISNUMERIC recognizes money formats and will convert those correctly.

I did. The 3rd value has a $...

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 9

Jason A. Long wrote:

Jeffrey Williams wrote:
You should add the money data type to this - it shows that ISNUMERIC recognizes money formats and will convert those correctly.
I did. The 3rd value has a $...

... and commas! 😀

--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)

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 10

be careful with the money data type - Vietnamese Dong tend to overflow after a certain value. (i'm talking building a hospital costs, not buying a chocolate bar)

MVDBA

m-scally SSC Journeyman Points: 99 More actions · Answer 11

You could try using TRY_CAST or TRY_CONVERT in your CASE but this will just hide the problem.

You're better to deal with the issue once then ignore it and deal with it constantly.

SELECT 
CASE 
WHEN 
ISNULL(TRY_CONVERT(INT, PROCSTATUS_NUM),0) = 1 
AND ISNULL(TRY_CONVERT(INT, AGE),0) >= ISNULL(TRY_CONVERT(INT, THRESHOLD),0)
THEN 0 
ELSE 4 
END AS Sort