I have created the following query below to list all invoices associated with a single check number:

SELECT * FROM APPAYMENT

where trans_nbr in

(select trans_nbr from cbcheck where trans_nbr = 633681
and appayment.cash_code = cbcheck.cash_code and appayment.bank_inst_code = cbcheck.bank_inst_code
and appayment.trans_ident = cbcheck.trans_ident)

I included a value to use as an example, but this will change to a parameter when in use by the ap users.

However, this query would take a long time to run if I wanted to use a check date range for multiple checks that were cut. How would you change the logic in the query to speed it up? I know I could create a new index in dbdef using trans-nbr and check-date, but didn't want to go there yet if I don't have to.

Any suggestions would be greatly appreciated...Thanks!

Mark Ivankovich

I'm not sure I understand the need for the nested query. Wouldn't this work by itself?

I ended up looking at the technical text at the relationships for the appayment and cbcheck file and came up with this one that is much faster, in fact, it's instant compared to the 13 sec subquery.

Thanks a lot for your insight!

select a.invoice, a.vendor from appayment a, cbcheck b
where b.trans_nbr = ' 633681'
and a.pay_group = b.pay_group
and a.cash_code = b.cash_code
and a.bank_inst_code = b.bank_inst_code
and a.trans_nbr = b.trans_nbr


Thanks,
Mark