when execute stuff i get error on order by

Question

when execute stuff i get error on order by

ahmed salah 3,216

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

Erland Sommarskog 134.8K Reputation points MVP Volunteer Moderator

2022-07-04T10:25:03.567+00:00

Make yourself a service and learn to use string_agg instead. And, no, I'm not going rewrite the code above for you. I've given you examples. Study and play with them. There is zero reason to use the bulky solution with FOR XML PATH, when you have string_agg.
Naomi Nosonovsky 8,906 Reputation points

2022-07-04T19:40:19.75+00:00

I agree with Erland, change the code to use STRING_AGG. The way it's currently written is very hard to understand and debug. If you need more help, post a simple scenario of your table structure and desired result.

Answer accepted by question author

Naomi Nosonovsky 8,906

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

ahmed salah 3,216 Reputation points

2022-07-04T22:42:50.017+00:00

thanks very much

1 additional answer

Your answer

Erland Sommarskog 134.8K Reputation points MVP Volunteer Moderator

2022-07-04T10:25:03.567+00:00

Make yourself a service and learn to use string_agg instead. And, no, I'm not going rewrite the code above for you. I've given you examples. Study and play with them. There is zero reason to use the bulky solution with FOR XML PATH, when you have string_agg.
Naomi Nosonovsky 8,906 Reputation points

2022-07-04T19:40:19.75+00:00

I agree with Erland, change the code to use STRING_AGG. The way it's currently written is very hard to understand and debug. If you need more help, post a simple scenario of your table structure and desired result.
ahmed salah 3,216 Reputation points

2022-07-04T22:42:50.017+00:00

thanks very much

Answer 1

Olaf Helper 47,621

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.

ahmed salah 3,216 Reputation points

2022-07-04T08:28:51.173+00:00

i need order by because features must be arranged according to feature value
Olaf Helper 47,621 Reputation points

2022-07-04T10:32:01.523+00:00

i need order by

You may need it in the outer query, but not in an inner sub query.
ahmed salah 3,216 Reputation points

2022-07-04T10:46:11.68+00:00

can you please show me if possible
Olaf Helper 47,621 Reputation points

2022-07-04T12:01:28.05+00:00

I already wrote it, remove that ORDER BY clause.

Share via

when execute stuff i get error on order by

1 additional answer

Your answer