I need to access and display the payment history for a bill similar to the "Transaction History - Bill" screen from the history menu item when displaying a bill.  The program will be driven from a CSV file which contains the bill refnum.  I need to display the history (check number, date paid, amount paid, open balance) for each bill.  Typically, there are upwards of 500 bills per file.

Thanks,

HerbR

This is a complex area, so I'm going to start with the simple answer. For the payment history only for Bill Ref. No. 9000 :-

I can extract the Payments from the BillPaymentCheckLine table by linking the Bill table and limiting the payment to Ref. No. 9000 by using:

SELECT BillPaymentCheckLine.TxnDate as Date, BillPaymentCheckLine.RefNumber as Number,
BillPaymentCheckLine.AppliedToTxnAmount as Payment, BillPaymentCheckLine.AppliedToTxnDiscountAmount as Discount
FROM Bill , BillPaymentCheckLine
Where Bill.TxnID=BillPaymentCheckLine.AppliedToTxnTXNID
and Bill.RefNumber='9000'

I tried running this code which would appear to do what I need.  It worked except each row took 2.5 to 3 minutes to complete.  So I guessed that the problem might be the reference to multiple tables (i.e. a join), so I split the request into two parts.  First I retrieved the desired Bills, including the TxnId and saved these in a memory table.  This took approximately 2 second per Bill (slow but usable). I then loop through the table (there are about 800 Bills) and retrieved the history with the following SQl:

SELECT TxnDate, RefNumber, AppliedToTxnAmount  INTO :checkdate, :checknumber, :payment
   FROM BillPaymentCheckLine
   WHERE AppliedToTxnTXNID = :txnid;

I write in Powerbuilder and the above syntax is correct.  The ":" indicates local client side variables.  Each SELECT takes 2.5 to 3 minutes.  That means the program would not finish until late tomorrow (about 30 hours!!!).  That is both not usable and REDICULUS.  I can do the process manually in front of a workstation faster.

HELP!!!

The main problem arrises because there's no optimized index (jump-in) on the AppliedToTxnTxnID column for the WHERE clause to use.

I've requested it as an enhancement to the QODBC Software Engineer. I don't have a time frame for the enhancement at this stage. I will post an update when the enchancement is done or if it's denied.

Try:

SELECT TxnDate, RefNumber, AppliedToTxnAmount  INTO :checkdate, :checknumber, :payment
   FROM BillPaymentCheckLine NOSYNC 
   WHERE AppliedToTxnTXNID = :txnid;

to manually force the query to run against the optimized table.

I just ran:

SELECT BillPaymentCheckLine.TxnDate as Date, BillPaymentCheckLine.RefNumber as Number,
BillPaymentCheckLine.AppliedToTxnAmount as Payment, BillPaymentCheckLine.AppliedToTxnDiscountAmount as Discount
FROM Bill , BillPaymentCheckLine
Where Bill.TxnID=BillPaymentCheckLine.AppliedToTxnTXNID

using QODBC v8.00.00.234 and got 161 rows returned within seconds.

Using sp_statistics BillPaymentCheckLine we can see that there is now a special QODBC optimized index to use for the AppliedToTxnTxnID lookups: