APVENMAST Duplicate Tax ID SN

 2 Replies
 0 Subscribed to this topic
 14 Subscribed to this forum
Sort:
Author
Messages
Ken Ptak
Basic Member
Posts: 20
Basic Member

    I'm attempting to build a SN that will return vendor information (Vendor Number, Name, Tax_ID, etc) for vendors who have duplicate tax IDs in the APVENMAST table.

    I have the following JDBC connection Infoset created that returns the Tax_IDs that exist in the APVENMAST more than one time, but I cannot figure out how to pull the Vendor Number, Vendor Name, etc in the same JDBC connection..  Any ideas/suggestions?

    SELECT APVENMAST.TAX_ID, Count(APVENMAST.TAX_ID) AS CountOfTAX_ID
    FROM APVENMAST
    GROUP BY APVENMAST.TAX_ID
    HAVING (((APVENMAST.TAX_ID)<>'                    ') AND ((Count(APVENMAST.TAX_ID))>1));

    Thanks,
    Ken

    John Henley
    Posts: 3353

      SELECT
      DUPES.TAX_ID,
      DUPES.CountOfTAX_ID,
      VEN.VENDOR_GROUP,
      VEN.VENDOR
      FROM (
      SELECT
      VEN.TAX_ID,
      Count(VEN.TAX_ID) AS CountOfTAX_ID
      FROM APVENMAST VEN
      WHERE VEN.TAX_ID <> ' '
      GROUP BY VEN.TAX_ID
      HAVING Count(VEN.TAX_ID)>1
      ) DUPES
      LEFT OUTER JOIN APVENMAST VEN
      ON VEN.TAX_ID = DUPES.TAX_ID
      ORDER BY TAX_ID, VENDOR_GROUP, VENDOR

      Thanks for using the LawsonGuru.com forums!
      John
      Greg Moeller
      Veteran Member
      Posts: 1498
      Veteran Member
        John: This would include the Inactive vendors, too, correct?