Chrissy321 (6/19/2013)
I appreciate you spending any time on this at all since it seems like a data issue,Below is more representative of the situation. Removing the WHERE statement and the query will run. Its when I include the CASE statement in the query and check if it is not null where the error is generated
What happens if you drop the case expression in the where clause? It really is not necessary because you have said that if the value is not 'ABC' then use the decimal conversion. This is the same thing as just checking for NULL.
select
*,
case SomeVal2 when 'abc' then 1 else CONVERT(decimal(20,6), SomeVal)END
from #MyData
WHERE SomeVal2 IS NOT NULL
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/