Skip to Content

Duplicate records query

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'

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Dec 23, 2016 at 11:10 AM

    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'
    Add comment
    10|10000 characters needed characters exceeded

  • Dec 23, 2016 at 09:25 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 23, 2016 at 10:41 AM

    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

    Add comment
    10|10000 characters needed characters exceeded