Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqldataguy
Starting Member
12 Posts |
 Posted - 2011-08-21 : 10:19:55
|
| I have a query in three parts with UNION ALL to combine the three parts into one result set. When I run it with all three parts I get this errorConversion failed when converting the varchar value 'N/A' to data type int.However, if I run any two of the parts (by commenting out the third) it runs fine. I have no explicit conversions and have (not yet anyway) been able to find any of my data fields returning 'N/A'. What do you think, is this one of those sql errors where the message is unrelated to the real problem? Or, of more immediate interest, any idea what the real problem might be? |
|
|
sqldataguy
Starting Member
12 Posts |
Posted - 2011-08-21 : 10:29:15
|
| Well, just typing out my question gave me a clue as to how to troubleshoot it. I went back through and commented out half of the fields in the SELECT for each part, until I narrowed it down to which field was generating the error. And sure enough, one of the records being pulled by one of the parts included the value 'N/A'. Adding a CASE statement to cope with this unexpected result allows the entiore query to run fine. And it had nothing to do with UNION ALL. Thanks for reading! |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-21 : 11:34:19
|
| keep in mind that corresponding columns in each of selects separated by UNION ALL should be of similar type.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
UNION ALL and Conversion Failed