SQL ORDER BY with UNION and

I am using SQL in Excel, my query has a UNION statement and I need to order by date.

I have 3 tables: "Work_order_0", "Work_history_0" and "Note_0". "Work_0rder_0" and "Work_history_0" have the same fields.

My code is:

SELECT Work_order_0.WO_Key AS 'WO key',
Work_order_0.Pos_key AS 'POS key',
Work_order_0.Order_date AS 'Order date',
Work_order_0.Order_time/86400 AS 'Order time',
Note_0.Note AS 'Note'
FROM API3.PUB.Work_order Work_order_0
LEFT JOIN API3.PUB.Note Note_0 ON Work_order_0.WO_Key=Note_0.Relate_key

UNION

SELECT Work_history_0.WO_Key AS 'WO key',
Work_history_0.Pos_key AS 'POS key',
Work_history_0.Order_date AS 'Order date',
Work_history_0.Order_time/86400 AS 'Order time',
Note_0.Note AS 'Note'
FROM API3.PUB.Work_history Work_history_0
LEFT JOIN API3.PUB.Note Note_0 ON Work_history_0.WO_Key=Note_0.Relate_key
ORDER BY 'Order date' DESC;


As you can see in the last line, I tried using an alias in the ORDER BY clause but it gives me the following error

Bad field specification in order by clause (7645)
​

Any suggestions? Thanks in advance.

Continue reading...