Skip to Content

JDT1 and OCRD QUERY showing duplicate results

Hi guys,

I have a query that links JDT1 and OCRD which is showing duplicate records for certain BPs.

I have changed the joins a couple of times but still get the same results. Need your help.

SELECT T1."CardCode" AS "BpCode", T1."CardName" AS "Name",T0."LineMemo" AS "DETAILS", "SYSDeb" AS "Debit Amount", "SYSCred" AS "Credit Amount", ("SYSDeb" - "SYSCred") AS "Balance Due", T0."RefDate", T0."Ref1",T1."Address", T1."Phone1", T1."CntctPrsn", T1."Fax", T1."E_Mail", "Ref2", "FCCurrency" AS "BP Currency" , T0."RefDate" as "Date", T2."Address" AS CRDADDRESS, T2."Address2", T2."Street", T2."City", T2."Country"

FROM JDT1 T0 LEFT JOIN OCRD T1 ON T0."ShortName" = T1."CardCode" LEFT JOIN CRD1 T2 ON T1."CardCode" = T2."CardCode"AND T1."CardType" = 'C'


WHERE T0."IntrnMatch" = '0' AND T0."BalDueDeb" <> T0."BalDueCred" 

Regards,

duplicates.png (12.6 kB)
Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Best Answer
    Dec 14, 2018 at 09:52 AM

    Hi Gaza,

    Try this query-

    SELECT Distinct T1."CardCode" AS "BpCode", T1."CardName" AS "Name",T0."LineMemo" AS "DETAILS", "SYSDeb" AS "Debit Amount", "SYSCred" AS "Credit Amount", ("SYSDeb" - "SYSCred") AS "Balance Due", T0."RefDate", T0."Ref1",T1."Address", T1."Phone1", T1."CntctPrsn", T1."Fax", T1."E_Mail", "Ref2", "FCCurrency" AS "BP Currency" , T0."RefDate" as "Date", T2."Address" AS CRDADDRESS, T2."Address2", T2."Street", T2."City", T2."Country"
    
    FROM JDT1 T0 
    LEFT JOIN OCRD T1 ON T0."ShortName" = T1."CardCode" 
    LEFT JOIN CRD1 T2 ON T1."CardCode" = T2."CardCode" 
    AND T1."CardType" = 'C' and T2.LineNum=(SELECT Max(CRD1.LINENUM) FROM CRD1 WHERE CRD1.CARDCODE=T1.CARDCODE)
    WHERE T0."IntrnMatch" = '0' AND T0."BalDueDeb" <> T0."BalDueCred" 
    
    

    Regards,

    Satish Gupta

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Satish,

      Edited it, final results below. Thanks a lot!

      SELECT Distinct T1."CardCode" AS "BpCode", T1."CardName" AS "Name",T0."LineMemo" AS "DETAILS", "SYSDeb" AS "Debit Amount", "SYSCred" AS "Credit Amount", ("SYSDeb" - "SYSCred") AS "Balance Due", T0."RefDate", T0."Ref1",T1."Address", T1."Phone1", T1."CntctPrsn", T1."Fax", T1."E_Mail", "Ref2", "FCCurrency" AS "BP Currency" , T0."RefDate" as "Date", T2."Address" AS CRDADDRESS, T2."Address2", T2."Street", T2."City", T2."Country"
      
      
      FROM JDT1 T0 
      LEFT JOIN OCRD T1 ON T0."ShortName" = T1."CardCode" 
      LEFT JOIN CRD1 T2 ON T1."CardCode" = T2."CardCode" 
      AND T1."CardType" = 'C' and T2."LineNum" = (SELECT Max(T3."LineNum") FROM CRD1 T3 WHERE T3."CardCode" = T1."CardCode")
      WHERE T0."IntrnMatch" = '0' AND T0."BalDueDeb" <> T0."BalDueCred" AND T1."CardCode" = 'CROC2008' AND T0."RefDate" > [%0]
      
  • Dec 14, 2018 at 06:22 AM

    Hi,

    I checked the query on the DEMO database and found that duplicate lines appeared for me but they were coming as the BP Code had multiple Addresses defined. So, the T2."Address" AS CRDADDRESS is different. May be you can share your requirement and then we can check what fields could be included to get the desired result.

    regards,

    Jitin

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Jitin,

      Thank you for responding. I have removed all addresses but still faced with the same issue. What i created was a customer statement that requires BP address as well as Ref 2 on JDT1.

      Regards,

      Tich

  • Dec 14, 2018 at 08:54 AM

    Hi Tichaona Gaza,

    Could you check this revised query:

    SELECT Distinct T1."CardCode" AS "BpCode", T1."CardName" AS "Name",T0."LineMemo" AS "DETAILS", "SYSDeb" AS "Debit Amount", "SYSCred" AS "Credit Amount", ("SYSDeb" - "SYSCred") AS "Balance Due", T0."RefDate", T0."Ref1",T1."Address", T1."Phone1", T1."CntctPrsn", T1."Fax", T1."E_Mail", "Ref2", "FCCurrency" AS "BP Currency" , T0."RefDate" as "Date", T2."Address" AS CRDADDRESS, T2."Address2", T2."Street", T2."City", T2."Country" FROM JDT1 T0 LEFT JOIN OCRD T1 ON T0."ShortName" = T1."CardCode" LEFT JOIN CRD1 T2 ON T1."CardCode" = T2."CardCode" AND T1."CardType" = 'C' WHERE T0."IntrnMatch" = '0' AND T0."BalDueDeb" <> T0."BalDueCred"

    Thank you,

    Aziz El Mir

    Add comment
    10|10000 characters needed characters exceeded