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

[rob_rubingh]

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] */

[rob_rubingh]

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.

[agustin_marcoscividanes]

Hi

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

Kind regards

Agustín Marcos Cividanes

[rob_rubingh]

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.

[former_member188716]

Because you need write (NOT LIKE) AND (INNER JOIN), you have errors of Incorrect syntax.

[agustin_marcoscividanes]

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