Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

SAP B1 Query

I have written a query to create a report for detail commissions by region. There are actually 8 line items for this invoice that I have selected. When I use a join statement to bring in the credits, the query only selects in 7 lines. When I take out the join, it selects 8 lines. Can anyone tell me why this happens? Here is my query:

SELECT T3.descript[Region], T6.State,T4.SlpName[Salesperson],T5.GroupName[Cust Group],T2.CardName[Customer],'Invoice'[Type],T1.DocNum[Invoice#],T1.DocDate, T0.LineTotal[Discounted Line Total], T0.DiscPrcnt[Line Discount],T1.DiscPrcnt[Order Discount], (T0.LineTotal - (T0.LineTotal * (T1.DiscPrcnt / 100))) [Discounted Total] FROM INV1 T0

INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T1.CardCode = T2.CardCode Left Outer JOIN OTER T3 ON T2.Territory = T3.territryID Left Outer JOIN OSLP T4 ON T2.SlpCode = T4.SlpCode Left Outer JOIN OCRG T5 ON T2.GroupCode = T5.GroupCode Left Outer JOIN CRD1 T6 ON T2.CardCode = T6.CardCode and T2.BillToDef = T6.Address

Where T1.Canceled = 'N' and T6.AdresType = 'B' and T1.DocNum = 23778

UNION

SELECT T3.Descript [Region],T6.State,T4.SlpName [SalesPerson],T5.GroupName [Cust Group],T7.CardName [Customer],'Credit' [Type],T7.DocNum [Invoice#], T7.DocDate,(T7.DocTotal-T7.TotalExpns)-1 [Discounted Line Total],0[Line Discount],T7.DiscPrcnt [Order Discount] ,(T7.DocTotal-T7.TotalExpns)-1[Discounted Total] FROM ORIN T7

INNER JOIN OCRD T2 ON T7.CardCode = T2.CardCode Left Outer JOIN OTER T3 ON T2.Territory = T3.territryID Left Outer JOIN OSLP T4 ON T7.SlpCode = T4.SlpCode Left Outer JOIN OCRG T5 ON T2.GroupCode = T5.GroupCode Left Outer JOIN CRD1 T6 ON T2.CardCode = T6.CardCode and T2.BillToDef = T6.Address

WHERE T6.AdresType = 'B' and T7.Canceled = 'N' and T7.DocNum = 23778

Thank you for your consideration.

Phyllis

Former Member
replied

Please use UNION ALL instead of UNION

Reason: UNION contains an implied DISTINCT operator that causes identical rows to disappear. This can lead to miscounts. The correct operator to use is UNION ALL:

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question