Prev
Next
Forums Integration / Customization S3 Customization/Development

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
Posts: 20
Basic Member
11/1/2007 12:45 PM
    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
    Posts: 3353
    11/1/2007 1:15 PM
      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
      Posts: 490
      Veteran Member
      1/28/2016 7: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?