Hi, INSERT INTO - SELECT doesn't work yet with CTEs, sorry. About the IndexOutOfBoundsException I'm not sure yet, could you post the stack trace?
Regards, Thomas On Wednesday, August 4, 2010, Stefan <[email protected]> wrote: > Hello group, > > I tried the new CTE feature and it works almost as expected. I have > two questions: > > Normal I have statements of the form: > INSERT INTO table1 (coll1, col2, ..) > SELECT col1, col2,... FROM table2 > > How can I insert the results of a CTE into a table? > I tried the following forms. > > INSERT... > WITH.. > SELECT... > > and > > WITH... > INSERT... > SELECT... > > None has worked. > > My second question is what does the error message > > General error: "java.lang.IndexOutOfBoundsException: Index: 0, Size: > 0" [50000-140] HY000/50000 (Help) > > mean? > > I get it when quite big results are created. But the result is > definitely finite. > > I used the following CTEs and both gave me this error. > > WITH TLink(sub, super, N) AS ( > SELECT sub, super, 1 > FROM subClass > UNION ALL > SELECT sub.sub, super.super, N+1 > FROM subClass AS sub > INNER JOIN TLink AS super > ON sub.super = super.sub > WHERE N<2 > ) > --INSERT INTO subClass (sub, super) > SELECT sub, super FROM TLink > > WITH TLink(sub, super) AS ( > SELECT sub, super > FROM subClass > UNION ALL > ( > SELECT sub.sub, super.super > FROM subClass AS sub > INNER JOIN TLink AS super > ON sub.super = super.sub > EXCEPT > SELECT sub, super FROM TLink) > ) > --INSERT INTO subClass (sub, super) > SELECT sub, super FROM TLink > > With these queries I wanted to save the transitive hull of a class > relationship. > > -- > You received this message because you are subscribed to the Google Groups "H2 > Database" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
