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!

Incermental values based on their previous result in a single query.,

Discussion in 'SQL PL/SQL' started by Vicky, Nov 18, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    how to increment values based on their previous result?

    select 100*level as seq from dual
    connect by level<=5;

    current o/p:

    SEQ
    ------
    100
    200
    300
    400
    500

    expected o/p:

    SEQ
    -----
    100 (100*1)=100
    200 (100*2)=200
    600 (200*3)=600
    2400 (600*4)=2400
    12000 (2400*5)=12000
     
    Last edited: Nov 24, 2015
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    100*level gives you what you received; there is no error or bug as the output is correct. Your expectations aren't based on the behavior of the expression since the 100 is a hard-coded value and won't change. You could try this:

    Code (SQL):
    SQL> SELECT seq*rownum FROM
      2  (SELECT 100*level AS seq FROM dual
      3  CONNECT BY level<=5);

    SEQ*ROWNUM
    ----------
           100
           400
           900
          1600
          2500

    SQL>
    but that doesn't give you what you want, either. You can, of course, manufacture your desired results:

    Code (SQL):
    SQL> SELECT seq FROM
      2  (SELECT 100*level AS seq FROM dual
      3  CONNECT BY level<=5)
      4  WHERE seq <= 200
      5  UNION
      6  SELECT seq*(rownum+1) FROM
      7  (SELECT 100*level AS seq FROM dual
      8  CONNECT BY level<=5)
      9  WHERE seq = 300
    10  UNION
    11  SELECT seq*(rownum+2) FROM
    12  (SELECT 100*level AS seq FROM dual
    13  CONNECT BY level<=5)
    14  WHERE seq = 400
    15  UNION
    16  SELECT seq*(rownum+5) FROM
    17  (SELECT 100*level AS seq FROM dual
    18  CONNECT BY level<=5)
    19  WHERE seq = 400
    20  UNION
    21  SELECT seq*(rownum+2)*10 FROM
    22  (SELECT 100*level AS seq FROM dual
    23  CONNECT BY level<=5)
    24  WHERE seq = 400;

           SEQ
    ----------
           100
           200
           600
          1200
          2400
         12000

    6 ROWS selected.

    SQL>
    There isn't a simple way to do what you want since you're referencing calculated values from the previous row.
     
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Yes David. As the result can be only manipulated based on the previous row., I achieved it using function.
    But he asked me to achieve it using the query.
    For your note, it is an interview question...
     
  4. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Is there any inbuilt Factorial function in Oracle..?!
     
  5. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi all.,

    I got the query which gives me the expected result, thru surfing..

    Code (SQL):

    SELECT round(EXP(SUM(ln(t1.n))))*100 AS seq
      FROM
      ( SELECT level n
          FROM dual
       CONNECT BY level <= 5) t1,
      ( SELECT level n
          FROM dual
       CONNECT BY level <= 5) t2
    WHERE t1.n<=t2.n
    GROUP BY t2.n
    ORDER BY t2.n;

    SEQ  
    -------
    100  
    200  
    600  
    2400  
    12000

     
    But I cant get Y they're using functions like exp,ln,sum.....
    Cud U help me in getting, how it works,.
     
  6. Sonu Yadav

    Sonu Yadav Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    India
    Well u can use lag function here to get the desired output

    SELECT ser,
    seq,
    lag ( seq,1,seq) over ( order by ser ) prev,
    ( lag ( seq,1,seq) over ( order by ser ) ) * ser prev_value
    FROM ( select rownum ser, 100*level as seq from dual
    connect by level<=5 )

    Hope it helped you
     
  7. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    No Sonu.,
    It's not giving me the expected result.,!