Cast function error from SQL Server 2008 view

Question

I have one table with alphanumeric codes. In this particular case, I’m not interested in codes that are alphanumeric in nature, only those that contain integers. I wrote a small query to find a subset of the codes I need:

select CAST(icd as float) as icd from ( select i.icd from icd as i where icd like '952%' or icd like '806%' or icd like '3440%' or icd like '3441' )t 

This returns me a list of codes that I need. However, when I try to use the where clause, for example:

where icd between 80600 and 80610 request failed, saying that it cannot convert the varchar data type to int. However, if I do something like

 select CAST(icd as float) as icd,icd+10 from ( select i.icd from icd as i where icd like '952%' or icd like '806%' or icd like '3440%' or icd like '3441' )t 

I can add ten to each code, and it starts, which means that they are actually intact. I want to use the where clause like this because the codes 80600-80610 must be labeled X and 80611-80620, they must be labeled Y. I can do this manually for each code, but I would like to be more extensible than that.

How can I make sure that SQL Server only views the view when using this where clause instead of failing?

Answer 1

Filters run before results are cast.

What you do is distinguish the results from the columns when the query is executed (and does all the filtering). Filters ( where ) are calculated before the result values ​​are returned.

To work with the same result set as in your example, the easiest way is to use CTE instead of a subquery, and then additional filtering:

 with codes as ( select cast(icd as int) as icd from icd where icd like '952%' or icd like '806%' or icd like '3440%' or icd like '3441' ) select icd, 'X' as label from codes where icd between 80600 and 80610 union all select icd, 'Y' from codes where icd between 80611 and 80620 

Whether CTEs are more / less readable than subqueries is a matter of endless discussion / argument. But in this case, when we reuse the same table expression to get the marked results of X and Y , it uses less code than what would be necessary if we did the same with the subqueries. In this case, it is much more readable.

The data to be produced must, of course, be numerical.

When you produce data, you must make sure that they can be distinguished from the type of result. If your value is 064 V , this is clearly not a number, so it cannot be distinguished. calling isnumeric can help here:

 with codes as ( select cast(icd as int) as icd from icd where isnumeric(icd) = 1 and ( icd like '952%' or icd like '806%' or icd like '3440%' or icd like '3441' ) ) select icd, 'X' as label from codes where icd between 80600 and 80610 union all select icd, 'Y' from codes where icd between 80611 and 80620 

Answer 2

your internal query will probably return varchar fields. Can you run

 select i.icd from icd as i where icd like '952%' or icd like '806%' or icd like '3440%' or icd like '3441' 

and make sure string data is not returned?

FYI, since you are using an internal query, you can add

 WHERE IsNumeric(t.icd ) = 1 

to be sure that only integers are returned

Answer 3

You need to make sure that your filter treats ICD as a string (which is), and then filters only those ICDs that are converted to numeric.

It should be especially noted that in string comparisons it is necessary to take into account the length of your number (for example, “8061” is between “80600” and “80610”), but, fortunately, it seems that your filter is inside a range whose length is constant.

eg.

 where icd between '80600' and '80610' -- ie exclude '12345' and 'Apple' and length(icd) = 5 -- ie exclude '8061' and '806000001' and isnumeric(icd) = 1 -- ie exclude '8060A'