when execute stuff i get error on order by

ahmed salah 3,216 Reputation points
2022-07-04T07:26:32.7+00:00

I work on sql server 2019 i have issue errors when execute statment below
stuff
but i don't know how to solve issue

select fd.partid,tt.PLID,tt.PartNumber,tt.CompanyName,tr.codetypeid,tt.codetype,tt.PlName,tr.code  
,count(fd.zfeaturekey) as CountFeatures  
,stuff(( SELECT  '$' + CAST( CP.FeatureName  AS VARCHAR(500)) AS [text()]  
                    FROM(SELECT distinct C.partid, C.FeatureName,C.ZfeatureKey from extractreports.dbo.collectallfeatures  C with(nolock)   
					where C.partid=fd.partid and C.CodeTypeID=fd.CodeTypeID and c.Code=fd.code and c.zplid=fd.zplid  
					ORDER BY C.ZfeatureKey)CP  
					  
					--ORDER BY CP.ZfeatureKey  
		  
                   FOR XML PATH('')), 1, 1, NULL) as FeatureName  
				   ,stuff(( SELECT  '$' + CAST( COALESCE(CP2.FeatureValue, 'NULL') AS VARCHAR(500)) AS [text()]  
                    FROM(SELECT distinct C2.partId,C2.zvalue as FeatureValue,C2.ZfeatureKey FROM extractreports.dbo.collectallfeatures C2 with(nolock)  
					where C2.partid=fd.partid and C2.CodeTypeID=fd.CodeTypeID and c2.Code=fd.code and c2.zplid=fd.zplid)CP2  
					where CP2.PartId=fd.partid  
					ORDER BY CP2.ZfeatureKey  
                    FOR XML PATH('')), 1, 1, NULL) as FeatureValue   
into extractreports.dbo.collectallfeatureswithfeaturename  
from extractreports.dbo.collectallfeatures fd   
inner join parts.tradecodes tr with(nolock) on tr.partid=fd.partid and tr.codetypeid=fd.codetypeid and fd.code=tr.code and tr.partlevel=0  
inner join #TempTradeCode tt on tt.partid=tr.partid and tt.codetypeid=tr.codetypeid  and tt.CurrentPL=tr.zplid   
  
group by fd.partid,tt.PLID,tt.PartNumber,tt.CompanyName,tt.codetype,tt.PlName,tr.code,tr.codetypeid  

error i get and i need to solve it

Msg 1033, Level 15, State 1, Line 6  
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.  
Msg 156, Level 15, State 1, Line 15  
Incorrect syntax near the keyword 'ORDER'.  
Msg 319, Level 15, State 1, Line 19  
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.  

so how to solve error above please

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,262 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
{count} votes

Accepted answer
  1. Naomi 7,361 Reputation points
    2022-07-04T19:57:13.487+00:00

    Since I don't have your tables, here is just the stub, you may want to play to adjust:

    select fd.partid,tt.PLID,tt.PartNumber,tt.CompanyName,tr.codetypeid,tt.codetype,tt.PlName,tr.code  
     ,count(fd.zfeaturekey) as CountFeatures  
     , STRING_AGG(FeatureName, '$') WITHIN GROUP (ORDER BY FeatureName ) AS FeatureName,  
     STRING_AGG(COALESCE(zValue, 'NULL'), '$') WITHIN GROUP (ORDER BY zFeatureKey) AS FeatureValue  
       
     into extractreports.dbo.collectallfeatureswithfeaturename  
     from extractreports.dbo.collectallfeatures fd   
     inner join parts.tradecodes tr with(nolock) on tr.partid=fd.partid and tr.codetypeid=fd.codetypeid and fd.code=tr.code and tr.partlevel=0  
     inner join #TempTradeCode tt on tt.partid=tr.partid and tt.codetypeid=tr.codetypeid  and tt.CurrentPL=tr.zplid   
          
     group by fd.partid,tt.PLID,tt.PartNumber,tt.CompanyName,tt.codetype,tt.PlName,tr.code,tr.codetypeid  
    

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 38,861 Reputation points
    2022-07-04T07:31:36.51+00:00

    FROM(SELECT distinct C.partid, C.FeatureName,C.ZfeatureKey from extractreports.dbo.collectallfeatures C with(nolock)
    where C.partid=fd.partid and C.CodeTypeID=fd.CodeTypeID and c.Code=fd.code and c.zplid=fd.zplid
    ORDER BY C.ZfeatureKey)CP
    The ORDER BY clause is invalid in views, inline functions,

    You got a clear error message, the ORDER BY above in the inline view ist not allowed.

    so how to solve error above please

    Easy, remove that ORDER BY.