Forum Stats

  • 3,838,768 Users
  • 2,262,398 Discussions
  • 7,900,755 Comments

Discussions

generate list with 30 step

marchello-OC
marchello-OC Member Posts: 31
edited Jan 17, 2014 10:39AM in SQL & PL/SQL

Hi,

my need is to generate lis with 30 step,

for example:

id

30

60

90

120

...

how do I perform this?

Thanks ahead.

Tagged:

Best Answer

Answers

  • Ramin Hashimzadeh
    Ramin Hashimzadeh Member Posts: 1,631 Silver Trophy
    Answer ✓
    select level*30 from dual
    connect by level <= 10
    

    ----

    Ramin Hasimzade

  • Hoek
    Hoek Member Posts: 16,087 Gold Crown

    You can also generate rows using recursive subquery factoring...but CONNECT BY is ofcourse much more intuitive.

    $> var max_n number
    $> exec :max_n := 150
    
    PL/SQL procedure successfully completed.
    
    $> with list(n) as ( select 30 n from dual union all
      2                    select n+30 from list where n+30 <= :max_n
      3                  )
      4  select n 
      5  from   list;
    
             N
    ----------
            30
            60
            90
           120
           150
    
    5 rows selected.
    
    $>  exec :max_n := 300
    
    PL/SQL procedure successfully completed.
    
    $> with list(n) as ( select 30 n from dual union all
      2                      select n+30 from list where n+30 <= :max_n
      3                    )
      4    select n 
      5    from   list;
    
             N
    ----------
            30
            60
            90
           120
           150
           180
           210
           240
           270
           300
    
    10 rows selected.
    
This discussion has been closed.