Hey all,
So im a bit stuck, im in a position where the Links section of database expert doesn't show any tables, thats a question i put in another thread so im working with an alternative.
Ive instead added a command with an SQL query to try and control the joins a bit more and get the information i need.
My goal is to create a maintenance report that can be accessed via each individual service call record in SAP B1, the report needs to show OSCL service call information as well INV1 rows for that particular service call. Additionally it needs to show all the equipment for that customer from the OINS table.
The issue that im having is both the INV1 rows returned and the OINS rows are returning unrelated records, amongst the correct ones. Below is my SQL query that im using, is there any better way to join them to return the information?
SELECT T1.callID, T1.BPBillAddr, T1.custmrName,T1.NumAtCard, T1.closeDate, T1.resolution,T3.ItemCode,T3.Dscription,T4.itemCode,T4.itemName FROM OCRD T0 LEFT OUTER JOIN OSCL T1 ON T1.customer = T0.CardCode LEFT OUTER JOIN OINV T2 ON T2.CardCode = T0.CardCode LEFT OUTER JOIN INV1 T3 ON T3.DocEntry = T2.DocEntry LEFT OUTER JOIN OINS T4 ON T4.customer = T0.CardCode