1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Query Builder (Store Procedure)

Discussion in 'SQL PL/SQL' started by keshav jain, Jul 10, 2014.

  1. keshav jain

    keshav jain Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Hi

    Note:- ............................= Significance of space

    I have a problem in creating a logic
    I have a table as following: Table Name:- Table1

    RowNo .....................Value
    1............................60
    2...........................29
    3...........................68
    4............................ 43
    5............................ 50

    I want to result in following format

    RowNo............................ Value............................ OutPut Formula
    1............................ -31............................ row2-row1
    2............................ 39............................ row3-row2
    3............................ -25............................ row4-row3
    4............................ 07............................ row5-row4
    5............................ 00............................ row5-row5

    So finally my problem is that how can I subtract upper row from lower row.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    use LEAD function.
    Simple example :
    Code (SQL):

    WITH your_data AS
    (
    SELECT 60 VALUE FROM dual UNION ALL
    SELECT 29 VALUE FROM dual UNION ALL
    SELECT 68 VALUE FROM dual UNION ALL
    SELECT 43 VALUE FROM dual UNION ALL
    SELECT 50 VALUE FROM dual
    )
    SELECT rownum rn,VALUE, lead(VALUE,1,VALUE) OVER (ORDER BY rownum) - VALUE sum_
    FROM your_data;

    SQL>
     
            RN      VALUE       SUM_
    ---------- ---------- ----------
             1         60        -31
             2         29         39
             3         68        -25
             4         43          7
             5         50          0