on 12-06-2016 2:51 PM
I have the following query I have constructed. I have been checking that all the information is showing up but I find there are 40 items are not listed when I execute the query. I am not clear on how to use the left outer joins and wonder if I should change one of them.
I need the query to show me all the Items in OITM as long as the item belongs to one of the product groups I designated in the WHERE statement, even if these items don't have information on one of the other linked tables. Please let me know how I should do this.
SELECT T0."ItemCode", T0."ItemName", T0."FrgnName", T0."SuppCatNum", T0."CardCode", T1."CardName", T1."Country", T0."InvntryUom",T0."LastPurPrc", T0."LastPurCur", T0."LastPurDat", MAX(T5. "DocDate"), T0."ReorderPnt", T0."LeadTime", T0."MinOrdrQty", T0."OrdrMulti", T0."OnHand", T0."IsCommited", T0."OnOrder", T3."PymntGroup", T4."Name", T4."E_MailL"
FROM OITM T0 LEFT OUTER JOIN OCRD T1 ON T0."CardCode" = T1."CardCode"
LEFT OUTER JOIN OCTG T3 ON T1."GroupNum" = T3."GroupNum"
LEFT OUTER JOIN OCPR T4 ON T1."CardCode" = T4."CardCode"
LEFT OUTER JOIN OINM T5 ON T0."ItemCode" = T5."ItemCode"
WHERE T0."ItmsGrpCod" = '108' OR T0."ItmsGrpCod" = '109' OR T0."ItmsGrpCod" = '110' OR T0."ItmsGrpCod" = '111' AND T1."CntctPrsn" = T4."Name"
GROUP BY T0."ItemCode", T0."ItemName", T0."FrgnName", T0."SuppCatNum", T0."CardCode", T1."CardName", T1."Country", T0."InvntryUom",T0."LastPurPrc", T0."LastPurCur", T0."LastPurDat", T0."ReorderPnt", T0."LeadTime", T0."MinOrdrQty", T0."OrdrMulti", T0."OnHand", T0."IsCommited", T0."OnOrder", T3."PymntGroup", T4."Name", T4."E_MailL"
User | Count |
---|---|
102 | |
12 | |
11 | |
6 | |
6 | |
4 | |
3 | |
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.