I am trying to write a query that will return APINVOICE, APDISTRIB, APVENMAST, and APPAYMENT information.
Basically the user wants the Paid invoice detail information including vendor name and check number & date.
I created the following query but it doesn't seem to completely work the way I would like it to do. It pulls the same invoice & distrib twice for each combination. In other words, if there is a single invoice with 2 distribution records, instead of the query returning 2 records it actually returns 4. this seems to happen when I include the part of the query where I add the logic to pull the appayment info (check date, number). If I remove the appayment stuff, the query seems to work fine.
Any thoughts?
select p.company, a.vendor, a.vendor_vname, p.invoice, p.invoice_dte, p.due_date, p.create_date,
p.distrib_date, d.dis_acct_unit, d.dis_account, y.bank_chk_amt, d.to_base_amt, y.check_date, y.trans_nbr
from uabhssd.apvenmast a,
uabhssd.apinvoice p,
uabhssd.appayment y,
uabhssd.apdistrib d
where (
p.company = d.company and
p.vendor = d.vendor and
p.invoice = d.invoice and
a.vendor = d.vendor and
y.company = p.company and
y.vendor = p.vendor and
y.invoice = p.invoice and
p.tran_paid_amt <> 0
)
At first glance, it looks like you are missing some join conditions for APDISTRIB/APINVOICE. Check the relations in the AP Data File Text doc...