First, let me state I have read various similar posts and haven't been able to identify the similarities between the problem that other posters have had with this error message and the situation I've encountered. Perhaps I'm not searching correctly, but here's the scenario. I'm trying to search for values in a table that are less than 70 when converted to a numeric value. Sometimes the value can be stored with a comma (i.e. 3,080 etc.) so I have a replace statement to remove the comma. The obsValue column in the queries below is varchar(2000) and I'm guessing that may have something to do with it. My initial query worked:
Select name, obsValue
From database.dbo.table
Where name in ('LDL')
and isnumeric(obsvalue) = 1
and cast(replace(obsvalue,',','') as decimal(18)) < 70
This brings back expected values, but it's not the only name I'm trying to search for. Other examples include ('LDL(CALC)'). Using a UNION statement will allow me to union queries together but unfortunately I don't control the application code and this is not an option. The only option I have available is using an IN clause, so ultimately the query will look like this when I'm searching for a variety of name values:
Select name, obsValue
From database.dbo.table
Where name in ('LDL', 'LDL(CALC)')
and isnumeric(obsvalue) = 1
and cast(replace(obsvalue,',','') as decimal(18)) < 70
And unfortunately doing it this way is where I get the error message. I apologize if this has already been answered elsewhere. Please link and I will give credit where credit is due.
You can replace the IsNumeric(obsvalue) with (select obsvalue where isnumeric(obsvalue) = 1).
Select name, obsValue
From database.dbo.table
Where name in ('LDL', 'LDL(CALC)')
and isnumeric(obsvalue) = 1
and cast(replace((select obsvalue where isnumeric(obsvalue) = 1),',','') as decimal(18)) < 70