the table has 7 a rows:
ID, Value
E1, yes
E2, 32.3
E3, 9
R1, 151
R2 40
R3, 20
R4, 99
I need to get the count of the rows where the values are in (R1, R2) and Value >= 151
If I select count(*) from new_data where ID in (R1, R2) it returns '2'
If I select value from new_data where ID in (R1, R2) it returns 151 and 49
If I select value from new_data where ID in (R1, R2) and Value >=151 it tells me it can't convert 'yes' to integer.
Tried making a CTE to tempData where ID in (R1, R2) then doing a select count(*) from tempData where Value >= 151 I get the same error.
However, select count(*) from tempData gives me the correct 2 and select value from tempData gives me 151 and 40 as you would expect. Any ideas?
Tried adding a convert, isnumeric, etc, no change.
The real issue is why is it even seeing the "yes" row when I add in the Value >=151 condition since any rows without valid values should have been filtered out of the tempData table?
Had to double-check this to see that I wasn't on a SQL Server site. Your problem is that SQL is trying to do an implicit conversion of all values and throws an error when it hits "yes".
You may want to try:
CASE WHEN ISNUMERIC(Value) = 1 THEN CAST(Value as int) ELSE 0 END as Value
That will force your non-numeric value data to evaluate to 0 and treat the rest as int (or whatever mixture of float/int/numeric you may need).
I'm assuming this is for one of those custom field table setups that are allowed in some systems. If so, I'd even consider creating a view out of it and saving it for that custom field, assuming that this particular field will always have numeric values.
Hi Peter -
MS SQL 2005. I tried that exact code previously - just shifted the error to a decimal value.
Not sure if the last comment posted or not. Have you tried pulling out all of the values for that particular custom field into a temp table and working with just that subset? Maybe cast as a numeric instead of int and/or add another case statement for decimals?
John,
What I think is going on is the Query Optimizer is taking your queries as suggestions only - you are trying to be explicit about "do this first then do this" but it is ignoring you and trying to optimize the query. In doing so at some point it is looking at your Value >= 151 test against the whole table and sees the non-numeric value and chokes. But I could be wrong. I was able to code a possible non-UDF solution that may work for you.
You may try the following (developed and tested on SQL 2008 only. I tested with Compatibility Level set to SQL 2005 and still worked.):
SELECT *
FROM
(SELECT ID,
Value OriginalValue,
CASE WHEN ISNUMERIC(Value) = 1 THEN CAST(Value AS decimal(5, 1))
ELSE NULL END ValueNumeric,
CASE WHEN ISNUMERIC(Value) = 0 THEN Value
ELSE NULL END ValueCharacter
FROM new_data) X
WHERE X.ID IN ('R1', 'R2')
AND X.ValueNumeric >= 121
Please let me know if this works or not. I have a couple more ideas if this doesn't.
Thanks,
Stephen
Did Stephen's comment answer why it happened for you? As Stephen said, the SQL is being evaluated for each value in your column in order to decide if it should be included (prior to being executed), and 'yes' >= '151' is not defined, so it chokes before it returns something.
Stephen, your solution should work on 2000 also, since you're using a derived table.
To me, it looks like a design flaw, if you're planning on storing numbers then use a corresponding datatype (int/float/decimal/whatever works best). This may not be possible if you can't change the design, and you'd need to clean your rows with 'yes'. Once you did that, your original query would work just fine.
Hope that helps,
Jon