CODEWITHSUNDEEP
sqlsql-serversql-order-by
Yavuz Selim Kayış
Yavuz Selim Kayış

Reputation: 33

sql order by incorrect syntax

DECLARE @MyTemp TABLE
(DatabaseName VARCHAR(MAX),
 RowNumber    INT
);
INSERT INTO @MyTemp
(RowNumber,
 DatabaseName
)
       SELECT RowNumber,
              DatabaseName
       FROM QUESINTERNATIONALCORP.dbo.GETVOLUMEQTYDATABASES();
DECLARE @loopLimit INT=
(
    SELECT TOP 1 RowNumber
    FROM @MyTemp
    ORDER BY RowNumber DESC
);
DECLARE @loopCounter INT= 1;
DECLARE @sqlInsertCommand NVARCHAR(MAX);
DECLARE @sqlSingleCommand NVARCHAR(MAX);
DECLARE @myTempTableName NVARCHAR(80);
DECLARE @SystemCurr NVARCHAR(3);
DECLARE @MainCurr NVARCHAR(3);
DECLARE @myTempleTableRealName NVARCHAR(MAX);
DECLARE @SystemCurrSql NVARCHAR(MAX);
DECLARE @MainCurrSql NVARCHAR(MAX);
DECLARE @myTempleTableRealNameSQL NVARCHAR(MAX);
WHILE(@loopCounter <= @loopLimit)
    BEGIN
        SET @myTempTableName =
(
    SELECT DatabaseName
    FROM @MyTemp
    WHERE RowNumber = @loopCounter
);
        SET @SystemCurrSql = N'SELECT @SystemCurrOut = 
    SysCurrncy
    FROM '+@myTempTableName+'.dbo.OADM';
        SET @MainCurrSql = N'SELECT @MainCurrOut = 
    MainCurncy
    FROM '+@myTempTableName+'.dbo.OADM';
        SET @myTempleTableRealNameSQL = N'SELECT @MainNameOut = 
    CompnyName
    FROM '+@myTempTableName+'.dbo.OADM';
        EXECUTE sp_executesql
                @SystemCurrSql,
                N'@SystemCurrOut NVARCHAR(3) OUTPUT',
                @SystemCurrOut = @SystemCurr OUTPUT;
        EXECUTE sp_executesql
                @MainCurrSql,
                N'@MainCurrOut NVARCHAR(3) OUTPUT',
                @MainCurrOut = @MainCurr OUTPUT;
        EXECUTE sp_executesql
                @myTempleTableRealNameSQL,
                N'@MainNameOut NVARCHAR(MAX) OUTPUT',
                @MainNameOut = @myTempleTableRealName OUTPUT;
        SET @sqlSingleCommand = '
(SELECT T0.DocEntry AS ''Order Id'',
       T0.CardName AS ''Customer Name'',
       T3.CardName AS ''Manufacturer Name'',
       T1.OpenQty AS ''Remain Order Qty'',
       ISNULL(T0.U_CH_RevExDate, T0.U_CH_CustExDate) AS ''Revised Ship Date'',
       DATEPART(wk, ISNULL(T0.U_CH_RevExDate, T0.U_CH_CustExDate)) AS ''Week'',
       YEAR(ISNULL(T0.U_CH_RevExDate, T0.U_CH_CustExDate)) AS ''Year''
FROM ORDR T0
     INNER JOIN '+@myTempTableName+'.dbo.RDR1 T1 ON T1.DocEntry = T0.DocEntry
     INNER JOIN '+@myTempTableName+'.dbo.POR1 T2 ON T2.BaseEntry = T1.DocEntry
                           AND T2.BaseLine = T1.LineNum
                           AND T2.BaseType = T1.ObjType
     INNER JOIN '+@myTempTableName+'.dbo.OPOR T3 ON T3.DocEntry = T2.DocEntry
     LEFT JOIN '+@myTempTableName+'.dbo.INV1 T4 ON T4.BaseEntry = T1.DocEntry
                          AND T4.BaseLine = T1.LineNum
                          AND T4.BaseType = T1.ObjType
     LEFT JOIN '+@myTempTableName+'.dbo.OINV T5 ON T5.DocEntry = T4.DocEntry
     LEFT JOIN '+@myTempTableName+'.dbo.PCH1 T6 ON T6.BaseEntry = T2.DocEntry
                          AND T6.BaseLine = T2.LineNum
                          AND T6.BaseType = T2.ObjType
     LEFT JOIN '+@myTempTableName+'.dbo.OHEM T7 ON T7.empID = T0.OwnerCode
     LEFT JOIN '+@myTempTableName+'.dbo.OUDP T8 ON T8.Code = T7.dept
     LEFT JOIN '+@myTempTableName+'.dbo.OITM T9 ON T9.ItemCode = T1.ItemCode
     LEFT JOIN '+@myTempTableName+'.dbo.OUBR T10 ON T10.Code = T7.branch
WHERE T0.Canceled = ''N''
      AND T3.CANCELED = ''N''
      AND T1.LineStatus = ''O''
ORDER BY T0.DocEntry) ';
IF(@loopCounter = 1)
            BEGIN
                SET @sqlInsertCommand = @sqlSingleCommand;
            END;
            ELSE
            BEGIN
                SET @sqlInsertCommand = @sqlInsertCommand+'UNION ALL '+@sqlSingleCommand;

            END;
        SET @loopCounter = @loopCounter + 1;
    END;

EXECUTE sp_executesql
        @sqlInsertCommand;

İt is my error:

(4 row(s) affected) Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'ORDER'.
Msg 156, Level 15, State 1, Line 57
Incorrect syntax near the keyword 'ORDER'.
Msg 156, Level 15, State 1, Line 85
Incorrect syntax near the keyword 'ORDER'.
Msg 156, Level 15, State 1, Line 113
Incorrect syntax near the keyword 'ORDER'.

It is my result:

(SELECT T0.DocEntry AS 'Order Id',
       T0.CardName AS 'Customer Name',
       T3.CardName AS 'Manufacturer Name',
       T1.OpenQty AS 'Remain Order Qty',
       ISNULL(T0.U_CH_RevExDate, T0.U_CH_CustExDate) AS 'Revised Ship Date',
       DATEPART(wk, ISNULL(T0.U_CH_RevExDate, T0.U_CH_CustExDate)) AS 'Week',
       YEAR(ISNULL(T0.U_CH_RevExDate, T0.U_CH_CustExDate)) AS 'Year'
FROM ORDR T0
     INNER JOIN CHERRYFIELDGMBHCOKG.dbo.RDR1 T1 ON T1.DocEntry = T0.DocEntry
     INNER JOIN CHERRYFIELDGMBHCOKG.dbo.POR1 T2 ON T2.BaseEntry = T1.DocEntry
                           AND T2.BaseLine = T1.LineNum
                           AND T2.BaseType = T1.ObjType
     INNER JOIN CHERRYFIELDGMBHCOKG.dbo.OPOR T3 ON T3.DocEntry = T2.DocEntry
     LEFT JOIN CHERRYFIELDGMBHCOKG.dbo.INV1 T4 ON T4.BaseEntry = T1.DocEntry
                          AND T4.BaseLine = T1.LineNum
                          AND T4.BaseType = T1.ObjType
     LEFT JOIN CHERRYFIELDGMBHCOKG.dbo.OINV T5 ON T5.DocEntry = T4.DocEntry
     LEFT JOIN CHERRYFIELDGMBHCOKG.dbo.PCH1 T6 ON T6.BaseEntry = T2.DocEntry
                          AND T6.BaseLine = T2.LineNum
                          AND T6.BaseType = T2.ObjType
     LEFT JOIN CHERRYFIELDGMBHCOKG.dbo.OHEM T7 ON T7.empID = T0.OwnerCode
     LEFT JOIN CHERRYFIELDGMBHCOKG.dbo.OUDP T8 ON T8.Code = T7.dept
     LEFT JOIN CHERRYFIELDGMBHCOKG.dbo.OITM T9 ON T9.ItemCode = T1.ItemCode
     LEFT JOIN CHERRYFIELDGMBHCOKG.dbo.OUBR T10 ON T10.Code = T7.branch
WHERE T0.Canceled = 'N'
      AND T3.CANCELED = 'N'
      AND T1.LineStatus = 'O'
**ORDER BY T0.DocEntry) UNION ALL** 


(SELECT T0.DocEntry AS 'Order Id',
       T0.CardName AS 'Customer Name',
       T3.CardName AS 'Manufacturer Name',
       T1.OpenQty AS 'Remain Order Qty',
       ISNULL(T0.U_CH_RevExDate, T0.U_CH_CustExDate) AS 'Revised Ship Date',
       DATEPART(wk, ISNULL(T0.U_CH_RevExDate, T0.U_CH_CustExDate)) AS 'Week',
       YEAR(ISNULL(T0.U_CH_RevExDate, T0.U_CH_CustExDate)) AS 'Year'
FROM ORDR T0
     INNER JOIN CHERRYFIELDTRADINGLTD.dbo.RDR1 T1 ON T1.DocEntry = T0.DocEntry
     INNER JOIN CHERRYFIELDTRADINGLTD.dbo.POR1 T2 ON T2.BaseEntry = T1.DocEntry
                           AND T2.BaseLine = T1.LineNum
                           AND T2.BaseType = T1.ObjType
     INNER JOIN CHERRYFIELDTRADINGLTD.dbo.OPOR T3 ON T3.DocEntry = T2.DocEntry
     LEFT JOIN CHERRYFIELDTRADINGLTD.dbo.INV1 T4 ON T4.BaseEntry = T1.DocEntry
                          AND T4.BaseLine = T1.LineNum
                          AND T4.BaseType = T1.ObjType
     LEFT JOIN CHERRYFIELDTRADINGLTD.dbo.OINV T5 ON T5.DocEntry = T4.DocEntry
     LEFT JOIN CHERRYFIELDTRADINGLTD.dbo.PCH1 T6 ON T6.BaseEntry = T2.DocEntry
                          AND T6.BaseLine = T2.LineNum
                          AND T6.BaseType = T2.ObjType
     LEFT JOIN CHERRYFIELDTRADINGLTD.dbo.OHEM T7 ON T7.empID = T0.OwnerCode
     LEFT JOIN CHERRYFIELDTRADINGLTD.dbo.OUDP T8 ON T8.Code = T7.dept
     LEFT JOIN CHERRYFIELDTRADINGLTD.dbo.OITM T9 ON T9.ItemCode = T1.ItemCode
     LEFT JOIN CHERRYFIELDTRADINGLTD.dbo.OUBR T10 ON T10.Code = T7.branch
WHERE T0.Canceled = 'N'
      AND T3.CANCELED = 'N'
      AND T1.LineStatus = 'O'
ORDER BY T0.DocEntry)

How can ı solve its

Upvotes: 0

Views: 207

Answers (1)

Aswani Madhavan
Aswani Madhavan

Reputation: 816

ORDER BY clause is invalid with UNION ALL, you can google and find work arounds for this problem

Upvotes: 2

Related Questions