SQL question on APINVOICE, APDISTRIB, APPAYMENT

 2 Replies
 0 Subscribed to this topic
 17 Subscribed to this forum
Sort:
Author
Messages
Karl Kemp
Basic Member Send Private Message
Posts: 20
Basic Member
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
)
John Henley
Send Private Message
Posts: 3351
Hi Karl, the problem is that you are doing an "inner join" (by default), which returns a combined row for all joined tables. You need to use an outer join to achieve the desired result. The syntax for outer joins differs from database vendor to database vendor, but if you're using Oracle you could probably do something like this (also, you were missing some fields in the joins):

select api.company, ven.vendor, ven.vendor_vname, api.invoice, api.invoice_dte, api.due_date, api.create_date,
api.distrib_date, apd.dis_acct_unit, apd.dis_account, app.bank_chk_amt, apd.to_base_amt, app.check_date, app.trans_nbr
from
uabhssapd.apinvoice api,
uabhssapd.appayment app,
uabhssapd.apdistrib apd,
uabhssapd.apvenmast ven
where (
api.company = apd.company (+) and
api.vendor = apd.vendor (+) and
api.invoice = apd.invoice (+) and
api.suffix = apd.suffix (+) and
api.cancel_seq = apd.cancel_seq (+) and
ven.vendor_group = api.vendor_group (+) and
ven.vendor = api.vendor (+) and
app.company = api.company (+) and
app.vendor = api.vendor (+) and
app.invoice = api.invoice (+) and
app.suffix = api.suffix (+) and
app.cancel_seq = api.cancel_seq (+) and
api.tran_paid_amt <> 0
)
Thanks for using the LawsonGuru.com forums!
John
Chesca
Veteran Member Send Private Message
Posts: 490
Veteran Member
John, using the SQL statement provided, could you help with SQL statement just to get any vendors that had payments of 600 or more in the year?