I have a seemingly simple problem but I think due to lack of sleep I am missing the obvious.

I have a table that contains Vendors, invoices and invoice amounts and I am trying to write a query to solve a problem with duplicate payments. To search for duplicates, I am needing to query the table and return only records where the Invoices #'s are different with the same transaction $ amount for the same vendor.

For example in the list below, the $10 and $25 dollar transactions would be included on the query results.

Vendor 1 INV1234 $10.00
Vendor 1 INV4567 $50.00
Vendor 1 INV8999 $10.00
Vendor 2 INV5555 $25.00
Vendor 2 INV6666 $25.00
Vendor 2 INV0000 $30.00

This message has been edited. Last edited by: kadkins,

Something along the lines of:

TABLE FILE ???
SUM COMPUTE TOTDOLLAR/P20.2M = DOLLARAMT ;
BY VENDOR
PRINT DOLLARAMT
      COMPUTE FILTER/A1 = IF TOTDOLLAR NE DOLLARAMT THEN 'Y' ELSE 'N' ;
BY VENDOR
BY INVOICE
WHERE TOTAL FILTER EQ 'Y'
END

Try this:
-* SORT the file by vendor and amount
TABLE FILE INVOICEFL
PRINT INVOICE_NO
BY VENDOR_NO
BY AMOUNT
ON TABLE HOLD AS MYFILE
END
-* PRINT THE DUPS
DEFINE FILE MYFILE
LST_INV/A10=IF VENDOR_NO EQ LAST VENDOR_NO AND AMOUNT EQ LAST AMOUNT THEN LAST INVOICE_NO ELSE '';
END
TABLE FILE MYFILE
PRINT INVOICE_NO LST_INV
BY VENDOR_NO
BY AMOUNT
WHERE VENDOR_NO EQ LAST VENDOR_NO AND AMOUNT EQ LAST AMOUNT AND INVOICE_NO NE LAST INVOICE_NO
ON TABLE PCHOLD
END

This will return the rows where the vendors are the same, the amounts are the same and the invoice numbers are different. Rows where all 3 fields are the same are not returned:

 TABLE FILE FPDATA
 COUNT INVOICE NOPRINT
    BY VENDOR
    BY DOLLARAMT
   SUM MAX.INVOICE NOPRINT
    BY VENDOR
    BY DOLLARAMT
    BY INVOICE
 WHERE TOTAL CNT.INVOICE GT 1
    ON TABLE SET HOLDLIST PRINTONLY
    ON TABLE HOLD
 END
-*
 TABLE FILE HOLD
 COUNT INVOICE 
    BY VENDOR
    BY DOLLARAMT
   SUM MAX.INVOICE NOPRINT
    BY VENDOR
    BY DOLLARAMT
    BY INVOICE
 WHERE TOTAL CNT.INVOICE GT 1 ;
 END

TABLE FILE ?????
SUM CNT.INVOICE NOPRINT
BY VENDOR NOPRINT
BY TRANSACTION_AMOUNT NOPRINT
PRINT VENDOR INVOICE TRANSACTION_AMOUNT
BY VENDOR NOPRINT
BY TRANSACTION_AMOUNT NOPRINT
WHERE TOTAL CNT.INVOICE GT 1;
END

lots of solutions
the solution of WAZ holds a little mistake, but is in the basic very simple...

BY INVOICE should be imho BY DOLLARAMT

Thanks to all who responded. I used a combo of some of the suggestions to get to the data I needed. Thanks!