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!

SQL to generate a column data at runtime with reference to an interval column

Discussion in 'SQL PL/SQL' started by yasar2002, Jan 8, 2014.

  1. yasar2002

    yasar2002 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Dubai
    A table contains data in this form

    Id Interval PM Suppress
    10 1 1yearly
    10 2 2yearly 1yearly
    10 4 4yearly 2yearly
    10 8 8yearly 4yearly

    The column suppress has to be generated at runtime using Oracle SQL in such a way that the interval field has to be considered. If there is only 1 row then suppress field has no value. If there are more than 1 rows then based on Interval field, it is expected to select previous PM value for each row.

    Please advise.

    Thanks,
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This would be a good use of the LAG() function to return the value from the previous row. A generic lag() example follows:


    Code (SQL):

    SQL> SELECT x.dte, nvl(x.price, lag(x.price) OVER (ORDER BY x.recid)) price
      2  FROM
      3  (SELECT recid, dte, nvl(price, lag(price) OVER (ORDER BY recid)) price
      4  FROM nulltest) x;


    DTE            PRICE
    --------- ----------
    01-MAR-11       1234
    04-MAY-11       1344
    11-MAY-11       1344
    13-JUL-11       2569
    23-SEP-11       3865
    24-SEP-11       3865
    03-OCT-11       3865
    16-NOV-11       4568


    8 ROWS selected.


    SQL>

    Possibly this can help you.