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
New Poster
New Poster
Congrats on posting!

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: 3363
New Poster
New Poster
Congrats on posting!
Engaged Reader
Engaged Reader
You are an engaged reader!
Avid Reader
Avid Reader
Avid Reader art thou!

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
MVP
MVP
You're an MVP!
New Poster
New Poster
Congrats on posting!
Engaged Reader
Engaged Reader
You are an engaged reader!
Avid Reader
Avid Reader
Avid Reader art thou!
John: This would include the Inactive vendors, too, correct?