Skip to Content
0
Sep 04, 2020 at 01:36 AM

Unwanted results in SAP B1 Query for linked Sales Order

43 Views

Hi all,

Ive got a problem that im sure has a simple solution but for the life of me its not jumping out at me. The results i am getting is every line item from a linked sales order to that service call. What should be happening is every line item from the most recent sales order linked to that service call.

Looking at the query, i can understand why its giving me all of them (the way its joined up) but i cant figure out the solution to fix it.

The query below;

SELECT T1."callID",T3."ItemCode", T3."Dscription", T3.DocEntry,T2.SrcvCallId,T3.Quantity,T4.DocNum
FROM OSCL T1
LEFT JOIN SCL4 T2 ON T1."callID" = T2."SrcvCallID"
INNER JOIN RDR1 T3 ON T2."Object" = T3."ObjType" AND T2."DocAbs" = T3."DocEntry" AND T2."Object" = '17'
INNER JOIN ORDR T4 ON T4.DocEntry = T3.DocEntry
WHERE T1.callID = 11255
ORDER BY T4.DocDate DESC

And the results;

So since that last row is from a previous sales order and not the most recent one, i dont want it to display.

As always any help is greatly appreciated.

Attachments

results1.jpg (69.7 kB)