on 10-06-2008 5:06 PM
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
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:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.