Home » SQL & PL/SQL » SQL & PL/SQL » LAG Function Logic (9iR2)
LAG Function Logic [message #385694] Tue, 10 February 2009 10:40 Go to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
I have the following scenario. I am attempting to show how application of invoices against a PO total decreases with each invoice application. Here is some sample data:

PO Amount   Invoice No.    Invoice Amount        Remaining PO Bal
$6000.00    ABCDEFG        $1500.00              $4500.00
            HIJKLMN        $1500.00              $3000.00
            OPQRSTU        $3000.00              $0.00



I have been playing around with the lag function, however I cannot get the results I want using lag because it will not include the first row data for Remaining PO Balance. I tried using a CASE statement when rownum=1 then use lag(expression, 0)
ELSE lag(expression, 1) but this approaches forces me to group by ROWNUM, which destroys the result set. Any ideas, thoughts?

Thank you,
Steve
Re: LAG Function Logic [message #385695 is a reply to message #385694] Tue, 10 February 2009 10:51 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
This is a perfect example of where a Test Case should be supplied along with any attempts that have been ... attempted.
Re: LAG Function Logic [message #385698 is a reply to message #385694] Tue, 10 February 2009 11:00 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Steve Corey wrote on Tue, 10 February 2009 17:40
Any ideas, thoughts?

Explore other analytic functions; maybe SUM (in its analytic form) is the one you need.
Re: LAG Function Logic [message #385700 is a reply to message #385695] Tue, 10 February 2009 11:09 Go to previous messageGo to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
I feel I provided a test case, I will provide the syntax for the initial attempt I have made. The query is a bit convulated due to certain requirements, but essentially what I am trying to do is take the PO_REVISED_AMOUNT column and subtract from that the ACCOUNTED_AMOUNT column for the first row (invoice 1). The second row should take the result of PO_REVISED_AMOUNT - ACCOUNTED_AMOUNT from row 1 and then subtract the ACCOUNTED_AMOUNT from row 2 from the derived value in row 1. This pattern should be followed for the remaining rows returned.

Attempt:
SELECT
     v.vendor_name                  PO_VENDOR,
     h.segment1                     PO_NUMBER,
     :p_gl_period                   GL_PERIOD,
     SYSDATE                        RUN_DATE,
     r.release_num                  PO_RELEASE_NUMBER,
     i.invoice_num                  INVOICE_NUMBER,
     NVL(orig.amount, 0)            PO_ORIGINAL_AMOUNT,
     NVL(rev.amount, 0)             PO_REVISED_AMOUNT,
     NVL(rev.amount, 0) -
      NVL(orig.amount, 0)           PO_CHANGE_AMOUNT,
     SUM(NVL(expended.amount, 0))   PAID_AMOUNT,
     SUM(NVL(actd.amount, 0))       ACCOUNTED_AMOUNT,
     SUM (NVL (actd.amount, 0) - 
      NVL (expended.amount, 0))     ACCOUNTED_NOT_PAID,
     SUM(NVL(liquid.amount, 0))     LIQUIDATED_AMOUNT,
     SUM(NVL(pend.amount, 0))       PENDING_AMOUNT,
     [B]NVL(rev.amount, 0) - lag(SUM(NVL(actd.amount, 0)), 1, 0)
        OVER (order by i.invoice_num) ACTIVE_BALANCE[/B]
    FROM
     po_headers_all                 h,
     po_lines_all                   l,
     po_line_locations_all          s,
     po_distributions_all           d,
     po_releases_all                r,
     ap_invoices_all                i,
     ap_invoice_distributions_all   ad,
     po_vendors                     v,
     (SELECT dist.po_header_id, dist.po_release_id,
             SUM(ROUND (dist.quantity_ordered * ship.price_override, 2)) amount
        FROM po_distributions_archive_all dist, po_line_locations_archive_all ship
       WHERE dist.revision_num = 0
        AND ship.revision_num = 0
        AND ship.line_location_id = dist.line_location_id
        AND dist.po_header_id = NVL (:p_po_id, dist.po_header_id)
       GROUP BY
        dist.po_header_id, dist.po_release_id
     ) orig,
     (SELECT dist.po_release_id, dist.po_header_id,
             SUM(ROUND (CASE WHEN dist.gl_cancelled_date <= LAST_DAY (TO_DATE (:p_gl_period, 'MON-YY'))
                         THEN 0
                         WHEN ship.closed_code = 'FINALLY CLOSED' AND dist.gl_closed_date <= LAST_DAY (TO_DATE (:p_gl_period, 'MON-YY'))
                         THEN dist.quantity_billed * ship.price_override
                    ELSE dist_arc.quantity_ordered * ship.price_override
                    END,
                 2)
                ) amount
        FROM po_line_locations_archive_all ship_arc,
             po_distributions_archive_all dist_arc,
             po_distributions_all dist,
             po_line_locations_all ship
       WHERE dist.po_distribution_id = dist_arc.po_distribution_id
         AND dist.po_header_id = NVL (:p_po_id, dist.po_header_id)
         AND ship.line_location_id = dist_arc.line_location_id
         AND ship_arc.line_location_id = dist_arc.line_location_id
         AND dist_arc.revision_num = (SELECT MAX (dist1.revision_num)
                                        FROM po_distributions_archive_all dist1
                                       WHERE TRUNC (dist1.gl_encumbered_date) <= LAST_DAY (TO_DATE (:p_gl_period, 'MON-YY'))
                                         AND dist1.po_distribution_id = dist_arc.po_distribution_id
                                     )
         AND ship_arc.revision_num = (SELECT MAX (ship1.revision_num)
                                        FROM po_line_locations_archive_all ship1
                                       WHERE ship1.line_location_id = ship_arc.line_location_id
                                         AND ship1.revision_num <= dist_arc.revision_num
                                     )
     GROUP BY dist.po_header_id, dist.po_release_id
     ) rev,
     (SELECT invoice_distribution_id, SUM (amount) amount
        FROM (SELECT inv_dist.invoice_distribution_id,
                     ROUND (SUM (  NVL (ae_line.entered_dr, 0)
                                 - NVL (ae_line.entered_cr, 0)
                                ),
                         2) amount
                FROM ap_invoice_distributions_all inv_dist,
                     po_distributions_all po_dist,
                     ap_invoice_payments_all pay,
                     ap_ae_lines_all ae_line,
                     ap_ae_headers_all ae
               WHERE po_dist.po_header_id = NVL (:p_po_id, po_dist.po_header_id)
                 AND po_dist.po_distribution_id = inv_dist.po_distribution_id
                 AND inv_dist.invoice_id = pay.invoice_id
                 AND ae.accounting_event_id = pay.accounting_event_id
                 AND ae.ae_header_id = ae_line.ae_header_id
                 AND ae_line.source_id = pay.invoice_payment_id
                 AND ae_line.source_table = 'AP_INVOICE_PAYMENTS'
                 AND ae_line.ae_line_type_code = 'LIABILITY'
                 AND ae_line.reference8 = inv_dist.distribution_line_number
                 AND LAST_DAY (TO_DATE (pay.period_name, 'MON-YY')) <= LAST_DAY (TO_DATE (:p_gl_period, 'MON-YY'))
                GROUP BY po_dist.po_distribution_id,
                         inv_dist.invoice_distribution_id
              )
       GROUP BY invoice_distribution_id
     ) expended,
     (SELECT inv.invoice_distribution_id, SUM (ROUND (inv.amount, 2)) amount
         FROM ap_invoice_distributions_all inv,
              po_distributions_all po,
              ap_invoices_all inv_head
        WHERE posted_flag = 'Y'
          AND inv_head.invoice_id = inv.invoice_id
          AND inv.po_distribution_id IS NOT NULL
          AND po.po_header_id = NVL (:p_po_id, po.po_header_id)
          AND inv.accounting_date <= LAST_DAY (TO_DATE (:p_gl_period, 'MON-YY')) -- accounting date not timestamped 
          AND po.po_distribution_id = inv.po_distribution_id
        GROUP BY inv.invoice_distribution_id
      ) actd,
      (SELECT aida.invoice_distribution_id, SUM(NVL(aela.accounted_cr, 0)) amount
         FROM ap_invoices_all aia, ap_invoice_distributions_all aida,
              ap_encumbrance_lines_all aela, gl_encumbrance_types glet,
              po_distributions_all poda
        WHERE aia.invoice_id = aida.invoice_id
          AND aida.invoice_distribution_id = aela.invoice_distribution_id
          AND aela.encumbrance_type_id = glet.encumbrance_type_id
          AND aida.po_distribution_id = poda.po_distribution_id
          AND UPPER(glet.encumbrance_type) = 'INVOICE'
          AND aida.accounting_date <= LAST_DAY (TO_DATE (:p_gl_period, 'MON-YY'))
          AND aida.posted_flag = 'Y'
          AND poda.po_header_id = NVL (:p_po_id, poda.po_header_id)
        GROUP BY aida.invoice_distribution_id
      ) liquid,
      (SELECT inv.invoice_distribution_id, SUM (ROUND (inv.amount, 2)) amount
         FROM ap_invoice_distributions_all inv, po_distributions_all po
        WHERE inv.po_distribution_id IS NOT NULL
          AND po.po_header_id = NVL (:p_po_id, po.po_header_id)
          AND (inv.accounting_date > LAST_DAY (TO_DATE (:p_gl_period, 'MON-YY'))
               OR posted_flag = 'N'
              )
          AND TRUNC(inv.creation_date) <= LAST_DAY (TO_DATE (:p_gl_period, 'MON-YY')) -- truncated creation date 07/02/08 SLC 
          AND po.po_distribution_id = inv.po_distribution_id
        GROUP BY inv.invoice_distribution_id
      ) pend 
    WHERE
     h.po_header_id = l.po_header_id
     AND l.po_line_id = s.po_line_id
     AND s.line_location_id = d.line_location_id
     AND d.po_distribution_id = ad.po_distribution_id
     AND ad.invoice_id = i.invoice_id
     AND h.po_header_id = r.po_header_id
     AND d.po_release_id = r.po_release_id
     AND h.po_header_id = orig.po_header_id
     AND r.po_release_id = orig.po_release_id
     AND h.po_header_id = rev.po_header_id
     AND r.po_release_id = rev.po_release_id
     AND h.vendor_id = v.vendor_id
     AND ad.invoice_distribution_id = expended.invoice_distribution_id
     AND ad.invoice_distribution_id = actd.invoice_distribution_id
     AND ad.invoice_distribution_id = liquid.invoice_distribution_id
     AND ad.invoice_distribution_id = pend.invoice_distribution_id(+)
     AND NVL(:p_po_id, h.po_header_id) = h.po_header_id
     AND (TRUNC(NVL(h.closed_date, SYSDATE)) > LAST_DAY (LAST_DAY (TO_DATE (:p_gl_period, 'MON-YY')) - 370)
          OR :p_po_id IS NOT NULL
         )    
    GROUP BY
     v.vendor_name,
     h.segment1,
     :p_gl_period,
     SYSDATE,
     r.release_num,
     NVL(orig.amount, 0),
     NVL(rev.amount, 0),
     NVL(rev.amount, 0) -
      NVL(orig.amount, 0),
     i.invoice_num


The output is attached. I have included a column at the very end of the report called 'Desired Output' that details my expectations of the output.
Re: LAG Function Logic [message #385703 is a reply to message #385700] Tue, 10 February 2009 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I feel I provided a test case,

Wrong!
Read Test case and post create table and insert statements along with the result you want with these data.

Regards
Michel
Re: LAG Function Logic [message #385713 is a reply to message #385694] Tue, 10 February 2009 14:02 Go to previous messageGo to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
OK, I'll just figure it out.

I will also avoid responding to posts that fail to meet these very same requirements.
Re: LAG Function Logic [message #385833 is a reply to message #385713] Wed, 11 February 2009 05:27 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
You seem annoyed. Can I ask why? As you said, they are very simple requirements, yet you seem to object to supplying them. Maybe I am mis-reading the tone of your response...
Re: LAG Function Logic [message #386171 is a reply to message #385833] Thu, 12 February 2009 23:52 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I am not sure why you think LAG is the key here. Instead, try doing the opposite, subtract out the analytic sum from your total figure. Here is an example:

from scott/tiger

SQL> desc emp
 Name                                                                                           
 --------------------------------------------------------------------------------------------
 EMPNO                                                                                          
 ENAME                                                                                          
 JOB                                                                                            
 MGR                                                                                            
 HIREDATE                                                                                       
 SAL                                                                                            
 COMM                                                                                           
 DEPTNO                                                                                         

SQL> l
  1  with
  2       sal_sum as (
  3                    select sum(sal) sum_of_sal
  4                    from emp
  5                  )
  6  select sal_sum.sum_of_sal
  7        ,sum(sal) over (order by empno)
  8        ,sal_sum.sum_of_sal-sum(sal) over (order by empno)
  9  from emp
 10*     ,sal_sum
SQL> /

SUM_OF_SAL SUM(SAL)OVER(ORDERBYEMPNO) SAL_SUM.SUM_OF_SAL-SUM(SAL)OVER(ORDERBYEMPNO)
---------- -------------------------- ---------------------------------------------
     29025                        800                                         28225
     29025                       2400                                         26625
     29025                       3650                                         25375
     29025                       6625                                         22400
     29025                       7875                                         21150
     29025                      10725                                         18300
     29025                      13175                                         15850
     29025                      16175                                         12850
     29025                      21175                                          7850
     29025                      22675                                          6350
     29025                      23775                                          5250
     29025                      24725                                          4300
     29025                      27725                                          1300
     29025                      29025                                             0

14 rows selected.
Good luck, Kevin

Or maybe I don't understand whay you really want because as others have suggested, you have not provided a sufficiently reproducable test case.

[Updated on: Thu, 12 February 2009 23:53]

Report message to a moderator

Previous Topic: Query on PL/SQL table using CAST
Next Topic: create view based on parameters
Goto Forum:
  


Current Time: Thu Dec 08 08:26:40 CST 2016

Total time taken to generate the page: 0.07341 seconds