Skip to Content
avatar image
Former Member

SAP B1 Query for Pick List

Hi Experts,

My Client want me to create a query that summing up all items and qnty in my pick list

now i have a query but it seem it does not work the items is grouping by but the qnty is not summing

can you help me guys thanks sorry for my english

here my query

SELECT SUM(T1.PickQtty) AS 'QTY.',T2.Dscription As 'SKU' FROM OPKL T0 INNER JOIN PKL1 T1 ON T0.AbsEntry = T1.AbsEntry INNER JOIN RDR1 T2 on T2.PickIdNo = T0.AbsEntry and T1.OrderLine = T2.LineNum AND T1.OrderEntry = T2.DocEntry INNER JOIN ORDR T3 ON T2.DocEntry = T3.DocEntry and T3.ObjType = T1.BaseObject INNER JOIN OITM T4 ON T2.ItemCode = T4.ItemCode WHERE T0.AbsEntry = '[%0]' GROUP BY T2.Dscription ORDER BY T2.Dscription

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jun 10, 2017 at 10:11 PM

    Hi Socrates,

    The idea of your query is correct, why you thinks that the result is not correct? I did some adjust on your query.

    SELECT 
    	SUM(T1.PickQtty) AS 'QTY.',
    	T2.Dscription As 'SKU' 
    FROM 
    	OPKL T0 
    	INNER JOIN PKL1 T1 ON T0.AbsEntry = T1.AbsEntry 
    	LEFT JOIN RDR1 T2 ON T2.DocEntry = T1.OrderEntry AND T1.OrderLine = T2.LineNum AND BaseObject = 17
    	LEFT JOIN ORDR T3 ON T2.DocEntry = T3.DocEntry and T3.ObjType = T1.BaseObject 
    WHERE 
    	T0.AbsEntry = '[%0]' 
    GROUP BY 
    	T2.Dscription 
    ORDER BY 
    	T2.Dscription
    

    Hope it helps.

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 12, 2017 at 04:06 AM

    Hi,

    No issue in your query. What is your expected result from your query?

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded