I have a complex QUERY that is executed correctly but SQL won't allow me to store it???!!! The error it throws says:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries and common table expression, unless TOP or FOR XML is also specified.
The Query is a join of 3 tables with 10 Lines, in which a 11th line, containing the totals has been added in each of the 3 tables with UNION ALL. So the structure is:
SELECT a.SalesUnit, a.Budget, b.SalesOrders, c.Turnover
FROM
(SELECT SalesUnit, SUM(Budget) FROM Budgettable GROUP BY SalesUnit)
UNION ALL
(SELECT 'Total' as SalesUnit, SUM(Budget) FROM Budgettable) as a
JOIN
(SELECT SalesUnit, SUM(SalesOrders) FROM SOTable GROUP BY SalesUnit)
UNION ALL
(SELECT 'Total' as SalesUnit, SUM(SalesOrders) FROM SOTable) as b on a.SalesUnit = b.SalesUnit
JOIN
(SELECT SalesUnit, SUM(Turnover) FROM TOTable GROUP BY SalesUnit)
UNION ALL
(SELECT 'Total' as SalesUnit, SUM(Turnover) FROM TOTable) as c on a.SalesUnit = c.SalesUnit
ORDER BY a.SalesUnit
I really do not understand the error message and what I am supposed to do in order to make it work.
Regards, Martin
Query is executed, but SQL won't store View
Posted - 07/11/2012 : 07:57:45
