May 20, 2003 at 1:08 pm
I'm trying to figure out how to do some conditional computation of numeric values in a field that contains numbers and characters in different rows. Here's what I tried to do:
CASE
WHEN PROD_RATIO = 'NA' THEN PROD_RATIO
ELSE CAST(CAST(PROD_RATIO AS FLOAT)/100 AS DECIMAL (9,3))
END AS [Production Ratio]
All the numeric values work fine,
BUT none of the character rows are returned, and SQL Query Analyzer gives this message too:
quote:
Error converting data type varchar to numeric
So I assume that SQL is trying to apply BOTH cases to All rows, then display the appropriate row.
Any ideas how to work around this given that I'm stuck with numbers in a Varchar field? SQL 7.0 btw.
Edited by - GregLyon on 05/20/2003 1:08:54 PM
May 20, 2003 at 1:39 pm
Nothing like replying to your own request! It turns out that I had to convert the result BACK to Varchar inside the CASE for it to work:
CASE
WHEN PROD_RATIO = 'NA' THEN PROD_RATIO
ELSE CAST(CAST(CAST(PROD_RATIO AS FLOAT)/100 AS DECIMAL (4,3)) AS VarChar(5))
END AS [Production Ratio]
Now that I have it working, can anyone explain this behavior? I would certainly appreciate it. Does SQL implicitly TYPE each output column, and it was trying to make this column a numeric? Thanks, Greg.
May 21, 2003 at 3:08 am
SQL Server has to decide what the data type of your [Production Ratio] column should be.
To do this, it looks at all the possible data types in your case statement, and chooses one according to a precedence list.
VARCHAR comes right near the bottom of the list, so SQL Server chose DECIMAL. There is no implicit conversion from VARCHAR to DECIMAL, so you have to do it yourself.
May 21, 2003 at 8:33 am
Thanks ian!
That explains a lot of quirks I've experienced when attempting to concatenate values. And sure enough BOL has the list of precedence, now that I know what to look for.
Coming from a VB background, where we have a concatenation operator and different rules of precedence, sure didn't help me in this case!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply