SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query is executed, but SQL won't store View
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

barnabeck
Posting Yak Master

Spain
201 Posts

Posted - 07/11/2012 :  07:57:45  Show Profile  Reply with Quote
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

webfred
Flowing Fount of Yak Knowledge

Germany
8768 Posts

Posted - 07/11/2012 :  08:25:01  Show Profile  Visit webfred's Homepage  Reply with Quote
The error message tells you to remove the ORDER BY if you want to make this a view.
Another trick is to do a SELECT TOP 100 PERCENT a.SalesUnit, a.Budget, ...


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

barnabeck
Posting Yak Master

Spain
201 Posts

Posted - 07/11/2012 :  09:10:53  Show Profile  Reply with Quote
Ahhh... that easy?! Thank you!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/11/2012 :  10:01:19  Show Profile  Reply with Quote
i didnt understand the relevance of ORDER BY here though. If its inside a view after SQL 2000 it wont guarantee order in which results are returned from view unless you use an explicit ORDER BY in select from view statement.

see

http://visakhm.blogspot.com/2010/01/behaviour-of-order-by-inside-view.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

barnabeck
Posting Yak Master

Spain
201 Posts

Posted - 07/11/2012 :  10:28:43  Show Profile  Reply with Quote
You are right Visakh16, it was not relevant at all. But I wouldn't see it in that moment
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000