question

rasingram2 avatar image
rasingram2 asked

case expression errors in where clause errors

Hello I have a problem i cannot work out in SQL Server 2012. This query 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') works fine, but this one 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) give me an error converting varchar to float. since the case expression works fine in the select clause, i cannot see why if doesn't in the where clause. Thank you for any help with this.
wherecast-convertcase
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
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)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kevin Feasel avatar image
Kevin Feasel answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rasingram2 avatar image
rasingram2 answered
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
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Are you sure you are working on SQL Server 2012?
0 Likes 0 ·
pretty sure, SQL Server 11.0.2100, running management studio 2012 on the server hosting the database.
0 Likes 0 ·
robbin avatar image
robbin answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rasingram2 avatar image
rasingram2 answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.