cancel
Showing results for 
Search instead for 
Did you mean: 

SAP B1 Query

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

former_member583013
Active Contributor
0 Kudos

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:

Former Member
0 Kudos

That fixed the problem. Thank you very much for your help, Suda.

Answers (1)

Answers (1)

Former Member
0 Kudos

Try this one to see if it works:


SELECT DISTINCT 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 ALL
SELECT DISTINCT T3.Descript Region,T6.State,T4.SlpName 
SalesPerson,T5.GroupName,T1.CardName Customer,'Credit' 
Type,T1.DocNum, T1.DocDate,(T1.TotalExpns - T1.DocTotal), 
0,T1.DiscPrcnt, (T1.TotalExpns - T1.DocTotal) 'Discounted 
Total' FROM RIN1 T0
INNER JOIN ORIN T1 ON T0.DocEntry = T1.DocEntry
LEFT JOIN INV1 T8 ON T0.BaseEntry = T8.DocEntry
LEFT JOIN OINV T9 ON T8.DocEntry = T9.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 T1.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 T1.Canceled = 'N' and 
T9.DocNum = 23778

Thanks,

Gordon