Hi Vijay

I've tried a few options on this one, and the only one that works is to create a temp table with the Numeric values (Excluding any alpha), then select from the temp table to get the required values.

The execution plan is always converting the query to a single table scan & outputting LineItemValue, so that's why it fails.

Why it's doing that & how to stop it, I don't know.

Examples: (which have the same problem)

SELECT BatchFileId, Num1

FROM

(

SELECT BatchFileId,

CAST(LineItemValue AS NUMERIC(28,5)) as Num1

FROM MyTable

WHERE LineItemValue NOT LIKE '%E%'

) X

WHERE Num1=1234.5670

SELECT T.BatchFileId,

X.Num1

FROM MyTable T

CROSS APPLY (SELECT CAST(T.LineItemValue AS NUMERIC(28,5)) as Num1

WHERE T.LineItemValue NOT LIKE '%E%') X

WHERE ISNUMERIC(X.Num1)=1 AND X.Num1=1234.5670

Maybe someone else knows what's happening & how to fix it?:ermm: