Wednesday, 17 September 2014

lead lag functions oracle sql

        LEAD function:-
=====================
Syntax:-
=======
LEAD(column, context, default) over partition clause

Note:-
=====
NEXT VALUE BASED ON CONTEXT -- TOP DOWN PROCESS --


 SQL Query:-

/* Formatted on 9/17/2014 4:39:57 PM (QP5 v5.115.810.9015) */
SELECT vendor_id,
       invoice_num,
       amount_paid amount,
       LEAD (amount_paid, 1, 0) OVER (ORDER BY amount_paid) paid_amount
FROM ap_invoices_all
WHERE invoice_currency_code = 'USD' AND ROWNUM <= 6

 

/* Formatted on 9/17/2014 4:40:05 PM (QP5 v5.115.810.9015) */
SELECT vendor_id,
       invoice_num,
       amount_paid amount,
       LEAD (amount_paid, 2, 0) OVER (ORDER BY amount_paid) paid_amount
FROM ap_invoices_all
WHERE invoice_currency_code = 'USD' AND ROWNUM <= 6

           LAG function:-
=====================
Syntax:-
=======
LAG (column, context, default) over partition clause 
Note:-
===== NEXT VALUE BASED ON CONTEXT -- BOTTOM UP PROCESS --

 SQL Query:-

 /* Formatted on 9/17/2014 4:40:05 PM (QP5 v5.115.810.9015) */
SELECT vendor_id,
       invoice_num,
       amount_paid amount,
       LAG (amount_paid, 1, 0) OVER (ORDER BY amount_paid) paid_amount
FROM ap_invoices_all
WHERE invoice_currency_code = 'USD' AND ROWNUM <= 6

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete