question

Firstly, if you have SQL 2012 then why are you using ISNUMERIC. We all know it does not always work as desired. For e.g. `ISNUMERIC(1,000,000)` would result in 1 but if you would try to convert it to FLOAT i.e. `SELECT CAST('1,000,000' AS FLOAT)`, it would give error. So use TRY_CONVERT instead. Secondly, Can you please clarify in words that what is your filter criteia i.e. WHERE clause. It is a bit confusing without use of proper parentheses. Not to mention, that in SELECT you were working on already filtered data set, but in WHERE clause all data records would be checked. So this is one of the major difference. Also, always use explicit type conversion while comparing static values. In your case recommended way is CASE WHEN isnumeric(RESULT) = 1 THEN CAST(RESULT.RESULT AS float) ELSE CAST(- 1 AS float) END > CAST(100 AS float)

In 2008 R2 (I don't have a 2012 machine to test against), this does work as you expect: create table dbo.Result ( sampno int, result varchar(150), acode varchar(150) ); insert into dbo.Result(sampno, result, acode) values (1, '99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999', 'SMOISTXL'), (1, 'Nonsense input', 'SMOISTXL'), (1, '9999', 'SMOISTXL'), (1, '#NUM', 'SMOISTXL'), (1, '500', 'SMOISTXL'), (1, '499.99', 'SMOISTXL'), (1, 'Flabbergasted', 'SMOISTXL'), (1, '#NUM!', 'SMOISTXL'), (1, '244.08', 'SMOISTXL'), (1, '17.60', 'SMOISTXL'), (1, '1', 'SMOISTXL'); create nonclustered index ix_Result on dbo.Result(result, acode) include(sampno); SELECT SAMPNO, CASE WHEN isnumeric(RESULT) = 1 THEN CAST(RESULT.RESULT AS float) ELSE - 1 END AS Expr1 FROM dbo.RESULT WHERE (ACODE = 'SMOISTXL') AND (RESULT = 'Nonsense Input') OR (ACODE = 'SMOISTXL') AND (RESULT = '9999') OR (ACODE = 'SMOISTXL') AND (RESULT = '#NUM!') OR (ACODE = 'SMOISTXL') SELECT SAMPNO, CASE WHEN isnumeric(RESULT) = 1 THEN CAST(RESULT.RESULT AS float) ELSE - 1 END AS Expr1 FROM dbo.RESULT WHERE ((ACODE = 'SMOISTXL') AND (RESULT = 'Nonsense Input')) OR ((ACODE = 'SMOISTXL') AND (RESULT = '9999')) OR ((ACODE = 'SMOISTXL') AND (RESULT = '#NUM!')) OR ((ACODE = 'SMOISTXL') AND (CASE WHEN isnumeric(RESULT) = 1 THEN CAST(RESULT.RESULT AS float) ELSE - 1 END > 100)) What causes failure is if you have a value like: insert into dbo.Result(sampno, result, acode) values (1, '$1', 'SMOISTXL'); **$1** is considered numeric by [SQL Server's ISNUMERIC function][1], but it does not convert to a float. In any event, I would recommend **not** using float, simply because float is not precise and can lead to floating-point imprecision, which could throw your reports off slightly. If this is money that you're getting and the culprit is symbols like the dollar sign or comma, you could convert to a money datatype instead of float. The ridiculously long result that I added in would cause an overflow error, but $1 would not cause an error. Like @Usman pointed out, though, if you're using SQL Server 2012, you can use [the TRY_CONVERT() method][2]. It will safely attempt a conversion, and should work fine for numeric conversions. [1]: http://msdn.microsoft.com/en-us/library/ms186272(v=sql.90).aspx [2]: http://msdn.microsoft.com/en-us/library/hh230993.aspx

the idea of the where clause is the return only records where ACODE='SMOISTXL' and RESULT is either '9999', '#NUM!', or a value greater than 100. I didn't use TRY_CONVERT because i didn't find any reference to it when searching for how to convert a string to a number in SQL. I have just tried: SELECT SAMPNO, try_convert(float, RESULT) AS Expr1 FROM dbo.RESULT and got an error saying 'try_convert' is not a recognised built-in function name

Rightly said by @Usman, the difference is working on all the values present in the table as compared to working on filetered rows after which @rasingram2 added the > 100 criteria. So @rasingram2 why don't you try this in two steps SELECT * FROM ( SELECT SAMPNO, CASE WHEN isnumeric(RESULT) = 1 THEN CAST(RESULT.RESULT AS FLOAT) ELSE - 1 END AS Expr1 , ACODE, RESULT FROM dbo.RESULT WHERE (ACODE = 'SMOISTXL') AND (RESULT = 'Nonsense Input') OR (ACODE = 'SMOISTXL') AND (RESULT = '9999') OR (ACODE = 'SMOISTXL') AND (RESULT = '#NUM!') OR (ACODE = 'SMOISTXL') ) FilteredData WHERE Expr1 > 100

Hello thanks for your help, i have just managed to narrow it down further. I over simplified the question, the whole query i was trying to run was: `SELECT SAMPNO, CASE WHEN isnumeric(RESULT) = 1 THEN CAST(RESULT.RESULT AS float) ELSE - 1 END AS Expr1 FROM dbo.RESULT WHERE (ACODE = 'SMOISTXL') AND (RESULT = 'Nonsense Input') OR (ACODE = 'SMOISTXL') AND (RESULT = '9999') OR (ACODE = 'BADMOIST') OR (ACODE = 'SMOISTXL') AND (RESULT = '#DIV/0!') OR (ACODE = 'SMOISTXL') AND (RESULT = '#VALUE!') OR (ACODE = 'SMOISTXL') AND (RESULT = '#NUM!') OR (ACODE = 'SMOISTXL') AND (CASE WHEN isnumeric(RESULT) = 1 THEN CAST(RESULT.RESULT AS float) ELSE - 1 END > 100)` and this gives the error, the code i put in the original question doesn't(i tried to get rid of as much of the unnecessary code to make my question easier to understand) it appears that the problem disappears when i use: `SELECT SAMPNO, CASE WHEN isnumeric(RESULT) = 1 THEN CAST(RESULT.RESULT AS float) ELSE - 1 END AS Expr1 FROM dbo.RESULT WHERE (ACODE = 'SMOISTXL') AND (RESULT = 'Nonsense Input') OR (ACODE = 'SMOISTXL') AND (RESULT = '9999') OR (ACODE = 'SMOISTXL') AND (RESULT = '#DIV/0!') OR (ACODE = 'SMOISTXL') AND (RESULT = '#VALUE!') OR (ACODE = 'SMOISTXL') AND (RESULT = '#NUM!') OR (ACODE = 'SMOISTXL') AND (CASE WHEN isnumeric(RESULT) = 1 THEN CAST(RESULT.RESULT AS float) ELSE - 1 END > 100)` or: `SELECT SAMPNO, CASE WHEN isnumeric(RESULT) = 1 THEN CAST(RESULT.RESULT AS float) ELSE - 1 END AS Expr1 FROM dbo.RESULT WHERE (ACODE = 'SMOISTXL') AND (RESULT = 'Nonsense Input') OR (ACODE = 'SMOISTXL') AND (RESULT = '9999') OR (ACODE = 'BADMOIST') OR (ACODE = 'SMOISTXL') AND (RESULT = '#DIV/0!') OR (ACODE = 'SMOISTXL') AND (RESULT = '#VALUE!') OR (ACODE = 'SMOISTXL') AND (RESULT = '#NUM!')` so the error is because of that one line, with the different ACODE, but that just confuses me even more, as it can display expr1 when ACODE='BADMOIST', just not run a comparison on it.