0

I have a complex query. Simplified, it looks like

select x,y... from table1 
UNION 
select x,y... from table2 
UNION 
select x,y... from table3 
UNION 
select x,y... from table4
UNION 
select x,y... from table5 
UNION 
select x,y... from table6

All these queries work fine when I run them individually. They even work fine when I use first 3 and last 3 queries in separate unions but it starts throwing divided by zero error when I run all of them together. Is there any workaround for this. It is just a temporary query for my research and analysis. So even an inefficient solution will work for now

John
  • 529
  • 8
  • 20
  • I think we need to see the full queries, can you include at least say two of them? My guess is that you're dividing by zero somewhere as a result of the `UNION`. – Tim Biegeleisen Dec 15 '16 at 06:05
  • 2
    Examine the execution plans. What is likely happening is that, when composed into the full union, some `SELECT` clause calculations are being pushed deeper (earlier) into the plan and are being evaluated in rows that the `WHERE` clauses should eliminate. *logically* there should be no error, but SQL Server sometimes charmingly violates the logical processing order and gives you an error instead. – Damien_The_Unbeliever Dec 15 '16 at 07:48
  • 1
    Temporary solution can be to use a temp table, just insert data from each query to your temp table, then query from temp table for final result – Kim Hoang Dec 15 '16 at 07:50
  • Yes Damien, I was thinking along similar lines, It must be some quirk of SQL Server. Thank You Kim, I will do what you suggested. – John Dec 15 '16 at 08:57
  • Are you worried about having duplicate rows as a result of the multiple queries? If not, try using `UNION ALL`. – iamdave Dec 15 '16 at 10:05
  • @iamdave, UNION ALL is also having the same issue. I have just worked around the problem by individually inserting results of all queries into a temp table and then removing dups. Good, it is only for analysis and not some prod process. – John Dec 16 '16 at 08:26

0 Answers0