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
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