Click here to Skip to main content
15,614,809 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I work on sql server 2019 i have issue errors when execute statment below
but i don't know how to solve issue

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.

What I have tried:

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
Updated 14-Jul-22 2:30am
CHill60 4-Jul-22 5:51am    
The error is quite clear - remove the ORDER BY in your sub-queries
ahmed_sa 4-Jul-22 6:30am    
i need it because it must feature name and feature value display order by ZfeatureKey

1 solution

Use the Reply link when responding to comments so that the poster is notified.

Error 1: You cannot have ORDER BY in your sub-queries - the language does not allow it except in the specific circumstances listed in the error message.
a) use FOR XML
b) Find an alternative method of producing this string e.g. Processing Loops in SQL Server[^]

Error 2: As for error 1. This might also be useful -How to Use 2 CTEs in a Single SQL Query |[^]

Error 3: SQL is not recognising parts.tradecodes as a table. Try fixing the first two errors and see if this one "goes away". If it does not then update your question with table schema, sample data and expected results and reply to this solution and I will have another look
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900