PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 01/28/2016 2:52 PM by  Chesca
SQL question on APINVOICE, APDISTRIB, APPAYMENT
 2 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Karl Kemp
Private
Private
Basic Member
(38 points)
Basic Member
Posts:20


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 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
    Private
    Private
    Senior Member
    (9641 points)
    Senior Member
    Posts:3231


    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 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
    Programmer/analyst
    State
    Veteran Member
    (1028 points)
    Veteran Member
    Posts:482


    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.