The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Question

User-797751191 posted

Hi

  In below code it is giving above error

Select * into #Tmp0 from (SELECT M.DocEntry,M.DocNum AS 'AP Inv. #', M.DocDate as 'Date',M.DocStatus,M.NumAtCard as 'Bill No. & Dt.', M.TaxDate as 'Document Date',
M.CardName as 'Vendor Name',A.GSTRegnNo'GST No',P.[Location] 'Location',
Sum(L.Quantity) as 'Quantity',
Sum(L.LineTotal) as 'Base Amt.(Rs.)',Max(L.VatPrcnt)'Tax Code'


FROM Test0 M Inner JOIN Test1 L on L.DocEntry=M.DocEntry
LEFT JOIN Test2 A on M.CardCode=A.CardCode 
INNER JOIN Test3 P on L.[LocCode]=P.
 

WHERE (M.DocDate >= '2019/04/05' AND M.DocDate <= '2019/04/05'  )

GROUP BY
M.DocEntry,M.DocNum,M.DocDate,M.DocStatus,M.NumAtCard,M.TaxDate,M.CardName,A.GSTRegnNo,P.[Location]
ORDER BY
M.DocEntry,M.DocNum,M.DocDate,M.DocStatus,M.NumAtCard,M.TaxDate,M.CardName,A.GSTRegnNo,P.[Location]
)a
Thanks

Answer 1

User-719153870 posted

Hi jsshivalik,

As suggested in the error message, ORDER BY is invalid ... unless TOP, OFFSET or FOR XML is also specified.

So the simplest solution here is to add TOP in your query, check below demo:

Select * into #Tmp0 from (SELECT top 100 percent M.DocEntry,M.DocNum AS 'AP Inv. #', M.DocDate as 'Date',M.DocStatus,M.NumAtCard as 'Bill No. & Dt.', M.TaxDate as 'Document Date',
M.CardName as 'Vendor Name',A.GSTRegnNo'GST No',P.[Location] 'Location',
Sum(L.Quantity) as 'Quantity',
Sum(L.LineTotal) as 'Base Amt.(Rs.)',Max(L.VatPrcnt)'Tax Code'


FROM Test0 M Inner JOIN Test1 L on L.DocEntry=M.DocEntry
LEFT JOIN Test2 A on M.CardCode=A.CardCode 
INNER JOIN Test3 P on L.[LocCode]=P.
 

WHERE (M.DocDate >= '2019/04/05' AND M.DocDate <= '2019/04/05'  )

GROUP BY
M.DocEntry,M.DocNum,M.DocDate,M.DocStatus,M.NumAtCard,M.TaxDate,M.CardName,A.GSTRegnNo,P.[Location]
ORDER BY
M.DocEntry,M.DocNum,M.DocDate,M.DocStatus,M.NumAtCard,M.TaxDate,M.CardName,A.GSTRegnNo,P.[Location]
)a
In
 addition, if add one top 100 percent didn't solve the issue, add it to all of your fields.
Below is a simple demo that can reproduce this issue and fixed it:
select * into #BBB from (select top 100 percent UID,UName from Users where UID<=5 and UName <> '' group by UID,UName order by UID,UName)b
Best
 Regard,
Yang Shen

Answer 2

User-797751191 posted

Hi Yang

    What top 100 percent does. 

Secondly 

)a wht is the function of a

Thanks

Answer 3

User753101303 posted

Hi,

What if you just move the ORDER BY clause after the subquery ie :

GROUP BY
M.DocEntry,M.DocNum,M.DocDate,M.DocStatus,M.NumAtCard,M.TaxDate,M.CardName,A.GSTRegnNo,P.[Location]
)a -- Subquery ends here now
ORDER BY
a.DocEntry,a.DocNum,a.DocDate,a.DocStatus,a.NumAtCard,a.TaxDate,a.CardName,a.GSTRegnNo,a.[Location]

You can't sort inside a subquery likely because it may have no effect at all depending on what you are doing next with this subquery. The TOP 100 PERCENT ORDER BY (IMO ugly) workaround used to work but doesn't any more since 2005 if I remember. The catch is that it is a SELECTION statement ie it takes the first x % and so you need to tell what exactly comes first. The db engine now sees you'll take all rows anyway and just skip doing a useless sort.

In short, ordering rows should be likely done at the very last moment.

Answer 4

User-797751191 posted

Hi Patrice

  Why we need to write 

)a

Thanks

Answer 5

User753101303 posted

This is an alias so that you can still use <alias>.<column name> if needed as you can with tables or views.