There are a couple of different methods that you can use to get over this hurdle. Some people may just call it quits, but I wouldn't want anyone to think that there is any limitation to SQL, because there really isn't. I have found over time that there is always a possible workaround.
Understanding why you receive the message above is equally as important as trying to find the solution. The logic of how the computer processes the commands above confuses the CPU because the WHERE statement and the CASE statement are both processed in the same transaction. So the computer reads the where statement as numeric and the case statement as varchar. The reason why the Case statement is read as varchar is because the writer of this query used Alpha characters within the case statement which originated as a numeric value.
The solution below has two different parts. The first is to Embed a SQL statement within a SQL statement. Take a look closer and see if you can spot the differences between the image below and the image above. You can see that the WHERE statement is not within the "Main" parentheses. This is an embedded select statement within another select statement.
By taking the where statement which is in numeric and embedding it, this forces the computer to compute the embedded statement first. Using the CAST() function allows you to convert the value to varchar which is processed by the machine after the where statement is processed. Thus you have successfully converted the value and prepared the value for the CASE statement.
I hope this helps! If you are still having questions, feel free to send me an email! Adios,
Error Converting Varchar to Numeric
One of the difficulties when writing advanced SQL Statements is trying to troubleshoot error messages provided by the SQL Server. Some of these error messages might look like they are in a foreign language.
This is often an error message that I see quite frequently:
Copyright © Dynamics GP Cloud. All rights reserved.