Duplicate records query

[vinayak_chavan]

Need country from below query , but getting double records.

pls chk and revert

SELECT t0.docentry,t0.ExcRmvTime,T0.PIndicator,T0.DOCNUM,T0.U_No_Of_Package,T1.U_Pack_Type,T0.U_Net_Weight,T0.U_Gross_Weight,T1.U_Mark_Package, T1.Quantity,t0.U_IR,t0.U_AdRemark,t1.u_ci,t1.U_CD,t1.U_INRPRICE,* FROM OOEI T0 INNER JOIN OEI1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode LEFT OUTER JOIN OHEM T3 ON T0.OwnerCode = T3.empID INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode --right JOIN OEI12 T5 ON T0.DocEntry = T1.DocEntry right JOIN CRD1 T5 ON T5.CardCode = T4.CardCode --INNER JOIN OCRY T6 ON T5.Country = T6.Code WHERE T0.DOCDATE >='2016-04-01 00:00:00.000' and t0.docnum='1755'

[Johan_H]

just fixed it 🙂

[Johan_H · Accepted solution]

Ok, how about this:

SELECT t0.docentry
      ,t0.ExcRmvTime
      ,T0.PIndicator
      ,T0.DOCNUM
      ,T0.U_No_Of_Package
      ,T1.U_Pack_Type
      ,T0.U_Net_Weight
      ,T0.U_Gross_Weight
      ,T1.U_Mark_Package
      ,T1.Quantity
      ,t0.U_IR
      ,t0.U_AdRemark
      ,t1.u_ci
      ,t1.U_CD
      ,t1.U_INRPRICE
      ,T5.Name AS Country
FROM OOEI T0 
     INNER JOIN OEI1 T1 ON T0.DocEntry = T1.DocEntry 
     INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode 
     LEFT OUTER JOIN OHEM T3 ON T0.OwnerCode = T3.empID 
     INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode
     LEFT OUTER JOIN OCRY T5 ON T4.MailCountr = T5.Code
WHERE T0.DOCDATE >='2003-04-01 00:00:00.000' and t0.docnum='1755'

[Johan_H]

Hi,

Ok, I see. Please give this a try:

SELECT t0.docentry
      ,t0.ExcRmvTime
      ,T0.PIndicator
      ,T0.DOCNUM
      ,T0.U_No_Of_Package
      ,T1.U_Pack_Type
      ,T0.U_Net_Weight
      ,T0.U_Gross_Weight
      ,T1.U_Mark_Package
      ,T1.Quantity
      ,t0.U_IR
      ,t0.U_AdRemark
      ,t1.u_ci
      ,t1.U_CD
      ,t1.U_INRPRICE
      ,T6.Name AS Country
FROM OOEI T0 
     INNER JOIN OEI1 T1 ON T0.DocEntry = T1.DocEntry 
     INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode 
     LEFT OUTER JOIN OHEM T3 ON T0.OwnerCode = T3.empID 
     INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode
     LEFT OUTER JOIN CRD1 T5 ON T4.CardCode = T5.CardCode 
                            AND T5.Address = 'Ship to' /*<-- you may have to change
                                                             'Ship to' to something
                                                             else, but it takes care 
                                                             of the double records */
     LEFT OUTER JOIN OCRY T6 ON T5.Country = T6.Code
WHERE T0.DOCDATE >='2003-04-01 00:00:00.000' and t0.docnum='1755'

Regards,

Johan

[vinayak_chavan]

Hi Johan,

country gives null record. and , its not possible to provide address.

we need to think on other way.

[Johan_H]

Hi,

Please try again like this:

SELECT t0.docentry
      ,t0.ExcRmvTime
      ,T0.PIndicator
      ,T0.DOCNUM
      ,T0.U_No_Of_Package
      ,T1.U_Pack_Type
      ,T0.U_Net_Weight
      ,T0.U_Gross_Weight
      ,T1.U_Mark_Package
      ,T1.Quantity
      ,t0.U_IR
      ,t0.U_AdRemark
      ,t1.u_ci
      ,t1.U_CD
      ,t1.U_INRPRICE
      ,* 
FROM OOEI T0 
     INNER JOIN OEI1 T1 ON T0.DocEntry = T1.DocEntry 
     INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode 
     LEFT OUTER JOIN OHEM T3 ON T0.OwnerCode = T3.empID 
     INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode 
WHERE T0.DOCDATE >='2016-04-01 00:00:00.000' and t0.docnum='1755'

Regards,

Johan

[vinayak_chavan]

HI Johan,

I need country of customer , i think crd1 & ocry table is missing .

pls chk .

thx in advance.