Skip to Main Content
  • Questions
  • inserting date using loop and incremnt

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, jamal.

Asked: February 19, 2016 - 4:34 am UTC

Last updated: February 19, 2016 - 10:12 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi
i have also sent u a question related to this
but now my question is
i am able to insert using

insert into TEST_TIME (time)
select to_char(d_day+.25,'DDHH24MI"Z"MONRR') days from tab_days;

but


how can i do it in a loop with an increment
ie first time increment is .25
for second time increment is .50 and so on


and Chris said...

Instead of a loop (row-by-row processing), it's better to do this in a single statement.

You can do this by using a row generator. This creates rows for the number of values you want to "loop". e.g. to have four increments of .25, you could do the following:

select rownum/4 inc from dual
connect by level <= 4;

       INC
----------
       .25
        .5
       .75
         1


To insert this, just cross join with your table, adding inc to the day:

insert into TEST_TIME (time)
  with rws as (
    select rownum/4 inc from dual
    connect by level <= 10 -- this is how many values you want to "loop"
  )
    select to_char(d_day+inc,'DDHH24MI"Z"MONRR') days 
    from rws, tab_days;


If you want to know more about row generators, check out:

https://www.youtube.com/watch?v=UonikfFgEyM

Is this answer out of date? If it is, please let us know via a Comment