August 30, 2011 at 11:36 am
I'm having a problem properly converting a value properly using a case statement. For some reason even though the check properly evaluates the CASE correctly and converts to varchar, the statement will still fail with a conversion error in the ELSE section even though it shouldn't ever get to that point.
We have a larger SQL Select statement that I've simplified to produce the same results.
SELECT ISNUMERIC(DataValue) ,'DataValue' =
CASE
WHEN ltrim(rtrim(DataValue)) in('.', '-', '-.', '+', '+.') OR ltrim(rtrim(DataValue)) like '%[^-+.0-9]%' THEN
CAST(DataValue as varchar(25))
ELSE
/* 'INT' --*/ CAST(DataValue as bigint)
END
FROM (Select datavalue = 'l999999999999999') a
When I run it like this I get the error "Error converting data type varchar to bigint." even though it should be caught by the WHEN section of the CASE statement it apparently is still evaluating the ELSE portion. We have currently split the select into two separate select statement that work but I would like to do it in one select instead. I've scoured through tons of web sites and can't find the solution anywhere.
If anyone has some thoughts I would appreciate it.
Thanks,
Roger
August 30, 2011 at 11:39 am
A CASE statement can only return a single datatype; it cannot return a varchar(25) on some rows and a BIGINT on other rows.
August 30, 2011 at 12:47 pm
Ok, I'll buy that. So given that fact, is there any other way to do this logically in one select statement and not have to break it out into two separate statements?
August 30, 2011 at 12:55 pm
How about:
SELECT ISNUMERIC(DataValue), DataValue
FROM (Select datavalue = 'l999999999999999') a
If you need to get actual int data and varchar data depending on the contents of a varchar field you will have to put those in two columns. You can't mix datatypes in a column in a select. It will attempt an implicit conversion which will not work with integers and character data (assuming it is characters and not numeric). That help?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 30, 2011 at 2:57 pm
We don't really care if the value is numeric other than we are inserting the data into a column with a sql_variant datatype and want to store the data as numeric if it passes isnumeric(), otherwise store it as a varchar.
I'm new to this project and not really sure of the business need for this, I was just asked to try to combine the two select statements into one. Has proven to be more difficult than I predicted originally.
August 30, 2011 at 3:11 pm
Then go back to your original statement and try this:
SELECT ISNUMERIC(DataValue) ,'DataValue' =
CASE
WHEN ltrim(rtrim(DataValue)) in('.', '-', '-.', '+', '+.') OR ltrim(rtrim(DataValue)) like '%[^-+.0-9]%'
THEN
cast(CAST(DataValue as varchar(25)) AS SQL_VARIANT)
ELSE
/* 'INT' --*/ cast(CAST(DataValue as bigint) AS SQL_VARIANT)
END
FROM (Select datavalue = 'l999999999999999') a
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 30, 2011 at 3:16 pm
Oh that sounds like an interesting piece of work. I have never really used the sql_variant. Back in the old VB (or FP for some) days when everything was a variant it just constantly caused issues when you weren't really sure what the datatype was supposed to be. You could really screw things up by checking = 55 instead of ="55".
What about something like this:
select CAST(55 as SQL_VAriant)
union all
select CAST('asdf' as sql_variant)
In essence can you just cast your source column as a sql_variant and let the sql engine determine how to store it? Should be interesting what works for this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 30, 2011 at 3:53 pm
Rogman (8/30/2011)
We don't really care if the value is numeric other than we are inserting the data into a column with a sql_variant datatype and want to store the data as numeric if it passes isnumeric(), otherwise store it as a varchar.I'm new to this project and not really sure of the business need for this, I was just asked to try to combine the two select statements into one. Has proven to be more difficult than I predicted originally.
I predict that if you store the data as a sql_variant you will regret it very soon.
August 30, 2011 at 4:05 pm
Beautiful! Worked like a charm. Always the simple things that seem to be the hardest.
Thanks to everyone for all the help!
August 30, 2011 at 4:06 pm
Michael Valentine Jones (8/30/2011)
Rogman (8/30/2011)
We don't really care if the value is numeric other than we are inserting the data into a column with a sql_variant datatype and want to store the data as numeric if it passes isnumeric(), otherwise store it as a varchar.I'm new to this project and not really sure of the business need for this, I was just asked to try to combine the two select statements into one. Has proven to be more difficult than I predicted originally.
I predict that if you store the data as a sql_variant you will regret it very soon.
No argument from me, however, like I said I just came on the project and this was already the business decision that has been made.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply