What could create a syntax error if you take a SQL query and perform an UNION with itself ?

Question

I have this strange error in SQL Server 2005 where I take a working query, add the UNION keyword below it and then copy the query again. In my opinion, this should always be working, but it is not. I get the message 'Incorrect syntax near the keyword 'union'.

What could create this problem ?

To be more specific, here is the complete query :

select distinct deliveries.id, orders.id, 20 + sum(orders.mass1) as allowed_duration 
from features_resources  
inner join features on features.id = featureid 
inner join orders on orders.id = features_resources.resourceid 
inner join orderinformations on orders.id = orderinformations.orderid
inner join deliveries on orderinformations.deliveryid = deliveries.id
where features.name = 'O_FRAIS'
and     (deliveries.ID IN
                      (SELECT     ID
                        FROM          dbo.DeliveriesInExportedSchedule))

group by deliveries.id, features.name ,orders.id order by deliveries.id
union
select distinct deliveries.id, orders.id, 20 + sum(orders.mass1) as allowed_duration 
from features_resources  
inner join features on features.id = featureid 
inner join orders on orders.id = features_resources.resourceid 
inner join orderinformations on orders.id = orderinformations.orderid
inner join deliveries on orderinformations.deliveryid = deliveries.id
where features.name = 'O_FRAIS'
and     (deliveries.ID IN
                      (SELECT     ID
                        FROM          dbo.DeliveriesInExportedSchedule))

group by deliveries.id, features.name ,orders.id order by deliveries.id 

I have tried to reproduce the error on a smaller query, by starting from a simple query and adding features one by one (inner join, nested queryes, group by, sum,....) but failed to reproduce the error again.

Any idea ?

Answer 1

Try putting the individual SELECTs in parentheses:

(SELECT ... )
UNION
(SELECT ... )

The way you have it now, the second WHERE and GROUP BY clauses are ambiguous - should that apply to the SELECT, or to the UNION? I don't have any way to tell, and neither has your DB server.

Answer 2

It is actually the order by deliveries.id in the top half that causes the problem.

The order by needs to apply to the whole query.

Example Syntax

SELECT v1.number
FROM   master.dbo.spt_values v1
WHERE  v1.number > 2000

UNION

SELECT   v2.number
FROM     master.dbo.spt_values v2
WHERE    v2.number < 10
ORDER BY v1.number