on 09-04-2020 2:36 AM
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.
Figured it out (Im getting better at answering my own questions). Solution below for anyone else who runs into the same issue. I additionally joined up the RDR1 with the SCL4 by looking at the DocPstDate of SCL4 with the MAX docdate from all the linked docs to the service call by object.
SELECT T1."callID",
T3."ItemCode" AS 'OrderCode',
T3."Dscription" AS 'OrderDesc',
T3.DocEntry,
T2.SrcvCallId,
T3.Quantity AS 'OrderQty',
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' AND T2.DocPstDate = (SELECT MAX(A.DocDate) FROM ORDR A INNER JOIN RDR1 B ON B.DocEntry = A.DocEntry INNER JOIN SCL4 C ON C.DocAbs = B.DocEntry AND C.Object = '17' WHERE C.SrcvCallID = T1.callID)
INNER JOIN ORDR T4 ON T4.DocEntry = T3.DocEntry
WHERE T1.callID = 11255
ORDER BY T4.DocDate DESC
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Nick,
glad to see your SQL skills are improving. I would only suggest that you instead use MAX(RDR1.DocEntry) for cases where you have two Sales Orders created at the same date. You can insert it as
(SELECT MAX(DocEntry) FROM RDR1 A INNER JOIN SCL4 B ON A.DocEntry = B.DocAbs AND B.Object = '17' WHERE B.SrvcCallID = T1.callID) T5
And then add in your WHERE T3.DocEntry = T5.DocEntry
BR,
Matija
User | Count |
---|---|
91 | |
10 | |
10 | |
6 | |
5 | |
5 | |
5 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.