APVENMAST Duplicate Tax ID SN

 2 Replies
 0 Subscribed to this topic
 14 Subscribed to this forum
Sort:
Author
Messages
Ken Ptak
Basic Member Send Private Message
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
Send Private Message
Posts: 3351

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 Send Private Message
Posts: 1498
Veteran Member
John: This would include the Inactive vendors, too, correct?