PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 01/28/2016 2:52 PM by  Chesca
 2 Replies
You are not authorized to post a reply.
Author Messages
Karl Kemp
Basic Member
(38 points)
Basic Member

Send Message:

11/01/2007 8:45 AM
    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, 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 ( = and
    p.vendor = d.vendor and
    p.invoice = d.invoice and
    a.vendor = d.vendor and = and
    y.vendor = p.vendor and
    y.invoice = p.invoice and
    p.tran_paid_amt <> 0
    John Henley
    Senior Member
    (9692 points)
    Senior Member

    Send Message:

    11/01/2007 9:15 AM
    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, 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
    uabhssapd.apinvoice api,
    uabhssapd.appayment app,
    uabhssapd.apdistrib apd,
    uabhssapd.apvenmast ven
    where ( = (+) 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 = (+) 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 forums!
    Veteran Member
    (1041 points)
    Veteran Member

    Send Message:

    01/28/2016 2:52 PM
    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?
    You are not authorized to post a reply.