[SOLVED] Developer's block

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,

Linux x86
Apache
Excel, CSV, HTML, PDF

Posts: 31 | Registered: September 29, 2010

Waz

Expert

posted

Hide Post

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

Waz...

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006

Ignored post by Waz posted

Show Post

PBrightwell

Master

posted

Hide Post

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

Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes

Posts: 755 | Location: TX | Registered: September 25, 2007

Ignored post by PBrightwell posted

Show Post

jgelona

Master

posted

Hide Post

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

In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.

Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006

Ignored post by jgelona posted

Show Post

Bethanne

Member

posted

Hide Post

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

Posts: 17 | Location: Colorado, USA | Registered: January 22, 2010

Ignored post by Bethanne posted

Show Post

FrankDutch

Virtuoso

posted

Hide Post

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

	*Frank*
	prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7