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

No comments:

Post a Comment