Solved:
Works in both db2 and h2 oracle.
create table DummyTabell ( x int );
insert into DummyTabell values (1);
insert into DummyTabell values (1);
insert into DummyTabell values (1);
insert into DummyTabell values (1);
insert into DummyTabell values (1);
insert into DummyTabell values (1);
insert into DummyTabell values (1);
insert into DummyTabell values (1);
insert into DummyTabell values (1);
insert into DummyTabell values (1);
select DATEADD('DAY', rownum(), '1999-12-31')
from DummyTabell
cross join DummyTabell
cross join DummyTabell
cross join DummyTabell
cross join DummyTabell
where DATEADD('DAY', rownum(), '1999-12-31') <= '2029-12-31'
Den måndagen den 9:e december 2013 kl. 16:43:26 UTC+1 skrev David Forsberg:
>
>
> Hi
>
> I want to produce multiple rows and insert those into a table.
>
> The goal is to create a calendartable, but H2 does not accept the SQL
> insert statement combined with recursive function calls.
>
> It worked in my db2 database though. Se below. Could someone please help
> me ?
>
> BR
> /David
>
> -- Example found on oracle forum, insert satement (red) does not work in
> H2 oracle. With.. works.
>
> INSERT INTO T1(ID)
> WITH RECURSIVE T(N) AS (
> SELECT 1
> UNION ALL
> SELECT N + 1 FROM T WHERE N < 10
> )
> SELECT N FROM T
>
>
>
> -- All below works in db2 environment, but not in H2 oracle.
>
> create table uppfoljning.KALENDER
> ( KALENDERDATUM timestamp not null
> );
>
>
> insert into uppfoljning.KALENDER WITH RECURSIVE cte_datetime(tms)
> AS
> (
> select DATEADD('DAY', 1, '2000-01-01')
> union all
> SELECT DATEADD('DAY', 1, tms)
> FROM cte_datetime
> WHERE tms < DATE '2030-12-31'
> ) select tms from cte_datetime
> ;
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
