CR Help - Showed duplicate records in CR

[Former Member]

Hi,

With the query below, some of the delivery show duplicate records in CR, but in SAP, there is only one records.

It happen on certain delivery, not all of them

For example:

In SAP:

Item | Delivery | Qty

A001 | 12345 | 10

In  CR:

Item | Delivery | Qty

A001 | 12345 | 10

A001 | 12345 | 10

Query:

SELECT 

T0.[DocDate], 

T0.[DocNum], 

T0.[CardName], 

T0.[CardCode], 

T1.[ItemCode], 

T1.[Quantity], 

T1.[Currency], 

T1.[PriceBefDi], 

T1.[LineTotal], 

T1.[TotalFrgn], 

T1.[StockPrice], 

T2.[AvgPrice], 

T3.[ItmsGrpNam], 

T2.[U_PROD_GROUP], 

T4.[Number], 

T0.[DocRate], 

T6.[DocNum], 

T5.[BaseType] 

FROM ODLN T0   

INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry 

LEFT JOIN OITM T2 ON T1.ItemCode = T2.ItemCode 

LEFT JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod 

LEFT JOIN OJDT T4 ON T0.TransId = T4.TransId 

JOIN INV1 T5 ON T1.TrgetEntry = T5.DocEntry and T1.linenum=T5.baseline and T1.TargetType=T5.ObjType 

JOIN OINV T6 ON T5.DocEntry = T6.DocEntry 

Thanks

[Former Member · Accepted solution]

Hi Hu,

Try with this,

SELECT Distinct 

T0.[DocDate], 

T0.[DocNum], 

T0.[CardName], 

T0.[CardCode], 

T1.[ItemCode], 

T1.[Quantity], 

T1.[Currency], 

T1.[PriceBefDi], 

T1.[LineTotal], 

T1.[TotalFrgn], 

T1.[StockPrice], 

T2.[AvgPrice], 

T3.[ItmsGrpNam], 

T2.[U_PROD_GROUP], 

T4.[Number], 

T0.[DocRate], 

T6.[DocNum], 

T5.[BaseType] 

FROM ODLN T0   

INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry 

LEFT JOIN OITM T2 ON T1.ItemCode = T2.ItemCode 

LEFT JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod 

LEFT JOIN OJDT T4 ON T0.TransId = T4.TransId 

JOIN INV1 T5 ON T1.TrgetEntry = T5.DocEntry and T1.linenum=T5.baseline and T1.TargetType=T5.ObjType 

JOIN OINV T6 ON T5.DocEntry = T6.DocEntry 

Thanks,

Harshal

[Former Member · Accepted solution]

Hi,

Thank you for the prompt replied.

But DISTINCT might remove the correct records also.

How to avoid this?

Thanks

[Former Member · Accepted solution]

Hi,

Distinct will only remove duplicate records and not correct one.

try running with distinct and without distinct and compare both result, your doubt will be clear.

Thanks,

Harshal

[Former Member · Accepted solution]

Hi,

Maybe my explanation was not clear enough.

I had run a test with distinct, it will remove the correct one in other delivery note with the same row records.

The row in delivery was correct, it just split into two rows because each is from different SO.

If apply distinct, it will remove one of the row. For this case, the distinct will not work.

Any suggestion?

Thanks

[Former Member · Accepted solution]

Hi Harshal,

Any clue?

Thanks

[Former Member · Accepted solution]

Hi all,

I added following field to identify the unique records.

T1.[BaseEntry],

T1.[BaseRef],

T1.[BaseLine]

It solved my problem

Thanks

[kothandaraman_nagarajan]

Hi,

Please try this query.

SELECT

T0.[DocDate],

T0.[DocNum],

T0.[CardName],

T0.[CardCode],

T1.[ItemCode],

T1.[Quantity],

T1.[Currency],

T1.[PriceBefDi],

T1.[LineTotal],

T1.[TotalFrgn],

T1.[StockPrice],

T2.[AvgPrice],

T3.[ItmsGrpNam],

T2.[U_PROD_GROUP],

T4.[Number],

T0.[DocRate],

T6.[DocNum],

T5.[BaseType]

FROM ODLN T0  

INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

LEFT JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod

LEFT JOIN OJDT T4 ON T0.TransId = T4.TransId

JOIN INV1 T5 ON T5.BaseEntry = T1.DocEntry and T1.linenum=T5.baseline and T1.TargetType=T5.ObjType

JOIN OINV T6 ON T5.DocEntry = T6.DocEntry

Thanks

[former_member227598]

Hi Olga Hu,

Please try below query.......

SELECT Distinct

T0.[DocDate],

T0.[DocNum],

T0.[CardName],

T0.[CardCode],

T1.[ItemCode],

T1.[Quantity],

T1.[Currency],

T1.[PriceBefDi],

T1.[LineTotal],

T1.[TotalFrgn],

T1.[StockPrice],

T2.[AvgPrice],

T3.[ItmsGrpNam],

T4.[Number],

T0.[DocRate],

T6.[DocNum],

T5.[BaseType]

FROM ODLN T0  

INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

LEFT JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod

LEFT JOIN OJDT T4 ON T0.TransId = T4.TransId

JOIN INV1 T5 ON T1.TrgetEntry = T5.DocEntry and T1.linenum=T5.baseline and T1.TargetType=T5.ObjType

JOIN OINV T6 ON T5.DocEntry = T6.DocEntry

Rgds,

Kamlesh Naware

[Former Member]

Hi,

Thank you for the prompt replied.

But DISTINCT might remove the correct records also.

How to avoid this?

Thanks

[Former Member]

Hi Kamlesh,

Any clue?

Thanks

[Former Member]

Hi Olga,

I think problem in T2.[U_PROD_GROUP].

Please check after comment T2.[U_PROD_GROUP],  field and use Distinct T0.[DocNum]

Regards

Bhanu

[Former Member]

Hi,

Why you think the problem is with T2.[U_PROD_GROUP]?

Anyway, what does it mean  to check after comment T2.[U_PROD_GROUP],  field?

DISTINCT might remove the correct records also.

How to avoid this?