T-SQL selecting values that match ISNUMERIC and also are within a specified range. (plus Linq-to-sql
- by Toby
I am trying to select rows from a table where one of the (NVARCHAR) columns is within a numeric range.
SELECT ID, Value
FROM Data
WHERE ISNUMERIC(Value) = 1 AND CONVERT(FLOAT, Value) < 66.6
Unfortunately as part of the SQL spec the AND clauses don't have to short circuit (and don't on MSSQL Server EE 2008). More info: http://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated
My next attempt was to try this to see if I could achieve delayed evaluation of the CONVERT
SELECT ID, Value
FROM Data
WHERE (CASE WHEN ISNUMERIC(Value) = 1 THEN CONVERT(FLOAT, Value) < 66.6 ELSE 0 END)
but I cannot seem to use a < (or any comparison) with the result of a CONVERT. It fails with the error
Incorrect syntax near '<'.
I can get away with
SELECT ID, CONVERT(FLOAT, Value) AS Value
FROM Data
WHERE ISNUMERIC(Value) = 1
So the obvious solution is to wrap the whole select statement in another SELECT and WHERE and return the converted values from the inner select and filter in there where of the outer select. Unfortunately this is where my Linq-to-sql problem comes in. I am filtering not only by one range but potentialy by many, or just by the existance of the record (there are some date range selects and comparisons I've left out.)
Essentially I would like to be able to generate something like this:
SELECT ID, TypeID, Value
FROM Data
WHERE (TypeID = 4 AND ISNUMERIC(Value) AND CONVERT(Float, Value) < 66.6)
OR (TypeID = 8 AND ISNUMERIC(Value) AND CONVERT(Float, Value) > 99)
OR (TypeID = 9)
(With some other clauses in each of those where options.) This clearly doesn't work if I filter out the non-ISNUMERIC values in an inner select.
As I mentioned I am using Linq-to-sql (and PredicateBulider) to build up these queries but unfortunately
Datas.Where(x => ISNUMERIC(x.Value) ? Convert.ToDouble(x.Value) < 66.6 : false)
Gets converted to this which fails the initial problem.
WHERE (ISNUMERIC([t0].[Value]) = 1) AND ((CONVERT(Float,[t0].[Value])) < @p0)
My last resort will have to be to outer join against a double select on the same table for each of the comparisons but this isn't really an idea solution. I was wondering if anyone has run into similar issues before?
