PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 09/01/2017 7:07 AM by  JoseLuis
MMdist
 2 Replies
Sort:
You are not authorized to post a reply.
Author Messages
JoseLuis
Application Analyst
Private
Basic Member
(12 points)
Basic Member
Posts:6


Send Message:

--
08/09/2017 8:18 AM

    We wrote the sql query below to pull non-stock item usage but it takes a long time to run. We added the sql to a Crystal Report and published it to our LBI dashboard for our Purchasing department to use. Is there a way to speed up this query?

     

     

    select

    pli.company,

    pli.po_number,

    pli.po_release,

    pli.po_code,

    pli.vendor,

    pli.line_nbr as line_nbr, 

    pcr.tot_order_amt,

    pcr.dropship_fl,

    pcr.open_po_amt,

    pcr.term_code,

    mmd.acct_unit,

    mmd.account,

    mmd.sub_acct,

    mmd.dist_company,

    mmd.activity,

    mmd.acct_category,

    mmd.system_cd,

    mmd.doc_type,

    mmd.line_seq,

    mmd.dist_percent,

    mmd.dist_qty,

    mmd.dist_amount,

    src.source_doc_n,

    gln.acct_unit,

    gln.description,

    pcr.dflt_src_doc,

    pcr.po_date,

    pcr.location,

    pli.line_nbr, pli.item,

    pli.description,

    pli.manuf_code,

    pli.manuf_division,

    pli.manuf_nbr,

    pli.ent_unit_cst,

    pli.ent_buy_uom,

    pli.quantity,

    ven.vendor_vname,

    pli.item_type,

    pli.location,

    pli.closed_fl

    from lawprod.poline pli

                   left join lawprod.mmdist mmd on  (pli.company = mmd.company

                                           and 'PO' = mmd.system_cd

                                           and pli.location = mmd.location

                                           and 'PT' = mmd.doc_type

                                           and pli.po_number = mmd.doc_number

                                            and pli.po_release = mmd.doc_nbr_num

                                            and pli.po_code = mmd.po_code

                                            and pli.line_nbr = mmd.line_nbr)     

        join lawprod.purchorder pcr on (pli.company = pcr.company

              and pli.po_number = pcr.po_number

              and pli.po_release = pcr.po_release

              and pli.po_code = pcr.po_code)

        left join lawprod.polinesrc src

           on (pli.company = src.company

             and pli.po_number = src.po_number

             and pli.po_release = src.po_release

             and pli.po_code = src.po_code

             and pli.line_nbr = src.line_nbr)

    left join lawprod.apvenmast ven

         on (pli.vendor = ven.vendor)

    left join lawprod.glnames gln

                   on (mmd.acct_unit = gln.acct_unit

                                  and mmd.company = gln.company)         

               where pli.item_type <> 'I'

                and po_date between {?begin_date} and {?end_date}

     


    Russ M
    BA
    Crowley
    Basic Member
    (28 points)
    Basic Member
    Posts:10


    Send Message:

    --
    08/09/2017 3:41 PM

    This should be pointing to the line Seq number on the mmdist table. Pointing to the line_nbr will create duplicate entries. 

    and pli.line_nbr = mmd.line_nbr)          


    Try using PO for your document type versus. 

    JoseLuis
    Application Analyst
    Private
    Basic Member
    (12 points)
    Basic Member
    Posts:6


    Send Message:

    --
    09/01/2017 7:07 AM

    Awesome, your suggestion worked. Thank you Russ!

     

    Why is PO preferable over PT for the document type?

    You are not authorized to post a reply.