Skip to Content
avatar image
Former Member

ORDER BY in combination with UNION without showing the actual field to be ordered by

Hi guys,

I'm quite new to writing SAP queries so excuse my ignorance :)

In below query I would like to sort by T4.[OnHand] Desc

But it's not working because of the UNION ALL part.

Sap is telling me T4.OnHand needs to be in the SELECT statement, but I don't want that because it is a list for some of our business partners that don't need to now our exact stock.

Any way around this ?

Thanks in advance.

SELECT
/*T4.[OnHand],*/ 
T1.[ItemCode],
T0.[ItemName],
T0.[SalUnitMsr] AS 'Eenheid',
T0.[Codebars],
T5.[ItmsGrpNam],
CASE 
WHEN T4.[OnHand] - T4.[IsCommited] > 50 THEN 'Meer dan 50' 
WHEN T4.[OnHand] - T4.[IsCommited] BETWEEN 26 AND 50 THEN 'Tussen 26 en 50' 
WHEN T4.[OnHand] - T4.[IsCommited] BETWEEN 11 AND 25 THEN 'Tussen 11 en 25' 
WHEN T4.[OnHand] - T4.[IsCommited] BETWEEN 6 AND 10 THEN 'Tussen 6 en 10'  
ELSE 'Tussen 1 en 5' END AS 'Voorraad',
T1.[Price] AS 'Lijstprijs',
CASE WHEN ISNULL(T3.[Price], 0) = 0 THEN 0.00 ELSE T3.[Price] END AS 'CAP'

FROM

OITM T0

INNER JOIN ITM1 T1 ON T0.[ItemCode] = T1.[ItemCode] and t1.PriceList = '23'
INNER JOIN OPLN T2 ON T1.[PriceList] = T2.[ListNum]
INNER JOIN ITM1 T3 ON T0.[ItemCode] = T3.[ItemCode] and t3.PriceList = '6'
INNER JOIN OITW T4 ON T0.[ItemCode] = T4.[ItemCode]
INNER JOIN OITB T5 ON T0.[ItmsGrpCod] = T5.[ItmsGrpCod]

WHERE

T0.frozenFor = 'N' AND T4.[WhsCode] = '01' AND T4.[OnHand] - T4.[IsCommited] > 0
AND T5.[ItmsGrpNam] NOT Like 'Z%'   /* Productgroepen die een Z bevatten overslaan (halffabrikaten) */
AND T5.[ItmsGrpNam] <> 'ALGDIVPRO'
AND T0.[ItemName] NOT Like '%kosten%'
AND T1.[ItemCode] NOT like '%AI'
AND T1.[ItemCode] NOT like '%SA100%'
AND T1.[ItemCode] NOT like '%S.DIV%'

UNION ALL

SELECT
/*T4.[OnHand],*/
T1.[ItemCode],
T0.[ItemName],
T0.[SalUnitMsr],
T0.[Codebars],
T5.[ItmsGrpNam],
'Uitverkocht',
T1.[Price],
CASE WHEN ISNULL(T3.[Price], 0) = 0 THEN 0.00 ELSE T3.[Price] END

FROM INV1 T6  
INNER JOIN OITM T0 ON T6.[ItemCode] = T0.[ItemCode] 
INNER JOIN ITM1 T1 ON T0.[ItemCode] = T1.[ItemCode] and t1.PriceList = '23'
INNER JOIN OINV T2 ON T6.[DocEntry] = T2.[DocEntry] 
INNER JOIN ITM1 T3 ON T0.[ItemCode] = T3.[ItemCode] and t3.PriceList = '6'
INNER JOIN OITW T4 ON T0.[ItemCode] = T4.[ItemCode]
INNER JOIN OITB T5 ON T0.[ItmsGrpCod] = T5.[ItmsGrpCod]

WHERE Convert(numeric, GetDate())-convert(numeric, T2.[DocDate])  < 14
AND T0.[OnHand] = 0

GROUP BY 

T1.[ItemCode],
T1.[Price],
T0.[ItemName],
T0.[SalUnitMsr],
T0.[Codebars],
T0.[OnHand],
T3.[Price],
T5.[ItmsGrpNam],
T4.[OnHand]

/*ORDER BY T4.[OnHand] DESC*/  /* THIS ONLY WORKS WHEN I SHOW T4.[ONHAND IN THE QUERY] */
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Nov 30, 2017 at 03:52 PM

    Hi

    try this query:

    SELECT * FROM

    (

    SELECT

    T4.[OnHand] as Onhand, T1.[ItemCode] as itemcode, T0.[ItemName] as itemname, T0.[SalUnitMsr] AS'Eenheid', T0.[Codebars] as codebars,

    T5.[ItmsGrpNam] as GroupName,

    CASE

    WHEN T4.[OnHand] - T4.[IsCommited] > 50 THEN 'Meer dan 50'

    WHEN T4.[OnHand] - T4.[IsCommited] BETWEEN 26 AND 50 THEN 'Tussen 26 en 50'

    WHEN T4.[OnHand] - T4.[IsCommited] BETWEEN 11 AND 25 THEN'Tussen 11 en 25'

    WHEN T4.[OnHand] - T4.[IsCommited] BETWEEN 6 AND 10 THEN'Tussen 6 en 10'

    ELSE'Tussen 1 en 5' END AS 'Voorraad',

    T1.[Price] AS 'Lijstprijs',

    CASE WHEN ISNULL(T3.[Price], 0)= 0 THEN 0.00 ELSE T3.[Price] ENDAS'CAP'

    FROM

    OITM T0

    INNER JOIN ITM1 T1 ON T0.[ItemCode] = T1.[ItemCode] and t1.PriceList ='23'

    INNER JOIN OPLN T2 ON T1.[PriceList] = T2.[ListNum]

    INNER JOIN ITM1 T3 ON T0.[ItemCode] = T3.[ItemCode] and t3.PriceList ='6'

    INNER JOIN OITW T4 ON T0.[ItemCode] = T4.[ItemCode]

    INNER JOIN OITB T5 ON T0.[ItmsGrpCod] = T5.[ItmsGrpCod]

    WHERE

    T0.frozenFor ='N'AND T4.[WhsCode] ='01'AND T4.[OnHand] - T4.[IsCommited] > 0

    AND T5.[ItmsGrpNam] NOTLike'Z%'/* Productgroepen die een Z bevatten overslaan (halffabrikaten) */

    AND T5.[ItmsGrpNam] <>'ALGDIVPRO'

    AND T0.[ItemName] NOTLike'%kosten%'

    AND T1.[ItemCode] NOTlike'%AI'

    AND T1.[ItemCode] NOTlike'%SA100%'

    AND T1.[ItemCode] NOTlike'%S.DIV%'

    UNION ALL

    SELECT

    T4.[OnHand] as Onhand,

    T1.[ItemCode] as itemcode,

    T0.[ItemName] as itemname,

    T0.[SalUnitMsr] AS'Eenheid',

    T0.[Codebars] as codebars,

    T5.[ItmsGrpNam] as GroupName,

    'Uitverkocht',

    T1.[Price] as'Lijstprijs',

    CASE WHEN ISNULL(T3.[Price], 0)= 0 THEN 0.00 ELSE T3.[Price] END as 'CAP'

    FROM INV1 T6

    INNERJOIN OITM T0 ON T6.[ItemCode] = T0.[ItemCode]

    INNERJOIN ITM1 T1 ON T0.[ItemCode] = T1.[ItemCode] and t1.PriceList ='23'

    INNERJOIN OINV T2 ON T6.[DocEntry] = T2.[DocEntry]

    INNERJOIN ITM1 T3 ON T0.[ItemCode] = T3.[ItemCode] and t3.PriceList ='6'

    INNERJOIN OITW T4 ON T0.[ItemCode] = T4.[ItemCode]

    INNERJOIN OITB T5 ON T0.[ItmsGrpCod] = T5.[ItmsGrpCod]

    WHEREConvert(numeric,GetDate())-convert(numeric, T2.[DocDate])< 14

    AND T0.[OnHand] = 0

    GROUP BY

    T1.[ItemCode],

    T1.[Price],

    T0.[ItemName],

    T0.[SalUnitMsr],

    T0.[Codebars],

    T0.[OnHand],

    T3.[Price],

    T5.[ItmsGrpNam],

    T4.[OnHand]

    ) S0

    ORDER BY S0.onhand

    Kind regards

    Agustín Marcos Cividanes

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 01, 2017 at 12:29 PM

    Hi

    write a space between INNER and JOIN, this an error by copypaste.

    Kind regards

    Agustín Marcos Cividanes

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 01, 2017 at 07:37 AM

    Thanks for your input, much appreciated. When I run it, it throw this error:

    1). [Microsoft][SQL Server Native Client 11.0][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near 'NOTLike'.2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'INNERJOIN'

    And.. I also would like to leave out the T4.[Onhand] in the SELECT statement. This is a list for our customers and they cannot see the exact stock.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 01, 2017 at 12:51 PM

    That works, BUT..

    I do not want:

    T4.[OnHand] as Onhand

    in the Query, because our clients shouldn't be able to see the exact stock.

    Add comment
    10|10000 characters needed characters exceeded