Skip to Content
0
Former Member
Nov 17, 2011 at 01:07 AM

SAP query returning duplicate records

786 Views

Hi all,

I'll try to elaborate as much as possible so here goes:

I have a query that I am trying to run in SAP B1 8.8 which is not returning the true set of results I am expecting.

Essentially I am trying to filter by product based on delivery notes. I have the query as follows -

SELECT count(*), sum(T0.[DocTotal]-T0.[VatSum]) as "Order Value"
FROM ODLN T0  INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
WHERE DateName(mm,T0.DocDate) = 'November'
and DateName(yyyy,T0.DocDate) = '2011'
and (T1.[GroupCode] = '102' or T1.[GroupCode] = '107') 

This returns the correct result of 1320 row numbers and the given sum.

When I try to break this query down further by product using the query below, the values are doubled, tripled, quadrupled etc and summed up to give a number that is extremely high.

The code is as follows:

SELECT distinct count (*), sum(distinct T0.[DocTotal]-T0.[VatSum]) as "Order Value" from DLN1 T1
inner join ODLN T0 on T0.DocEntry = T1.DocEntry
Inner join OITM T2 on T2.ItemCode = T1.ItemCode 
inner join OCRD T3 on T0.CardCode = T3.CardCode 
WHERE DateName(mm,T0.DocDate) = 'November'
and DateName(yyyy,T0.DocDate) = '2011'
and (T3.[GroupCode] = '102' or T3.[GroupCode] = '107')
and (T2.[ItmsGrpCod] = '108' or T2.[ItmsGrpCod] = '112' or T2.[ItmsGrpCod] = '115')

This also returns 1819 rows where there should be less based on the product.

Please let me know if I am not explaining myself well or need further clarification.

Thanks!