Hello,
We're currently integrating support for CTE in jOOQ and we're wondering if
we should support CTE for H2 at all. H2 has experimental CTE support, if
I'm not mistaken:
http://www.h2database.com/html/advanced.html?highlight=recursive&search=recursive#recursive_queries
This would be one of our test cases, and it seems to work for H2:
with recursive "t1"("f1", "f2") as (
select
1,
'a'
from dual
union all
select
("t1"."f1" + 1),
("t1"."f2" || 'a')
from "t1"
where "t1"."f1" < 10
)
select
"t1"."f1",
"t1"."f2"
from "t1"
However, in standard SQL, I can declare several tables in the WITH clause,
e.g.:
with recursive "t1"("f1", "f2") as (...),
"t2"("g1", "g2") as (...),
...
select ...
This doesn't seem to work right now for H2.
Am I right in thinking that:
1. Only RECURSIVE CTE are currently supported, although I can tweak a
synthetic UNION ALL clause into the query to make H2 believe that we have
the required syntax (see below)
2. The RECURSIVE keyword seems to be optional - probably to be Oracle
compatible as in Oracle, recursiveness is implicit
3. Only single-table CTE are currently supported
4. This is currently still not a priority for the H2 maintenance team? (as
this question occasionally pops up on the user-group) :-)
[From 1] Non-recursive tweak to comply with H2 syntax requirements:
with "t1"("f1", "f2") as (
select
1,
'a'
from dual
union all
select null, null
where false
)
select
"t1"."f1",
"t1"."f2"
from "t1"
Any feedback is very welcome.
Cheers
Lukas
--
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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
