Recursive query with H2

Question

I'm trying to create a recursive query with H2, but I can't seem to get it work.

The recursive query I'm trying to get working is something along the lines of the answer from this previous question I posed to StackOverflow. I'm trying to create a query for JUnit testing since H2's inability to create a true stored procedure (Java code dealing with result sets isn't a great answer as it completely skips most of my code under test).

I've thoroughly read the H2 documentation and tried something basic, which is to use one of the examples from the H2 documentation. What I found is that the recursive query shown in the H2 documentation doesn't work when I run it in the console :(

I"m using the second example from the documentation link, which is:

WITH cte1 AS (
        SELECT 1 AS FIRST_COLUMN
), cte2 AS (
        SELECT FIRST_COLUMN+1 AS FIRST_COLUMN FROM cte1
)
SELECT sum(FIRST_COLUMN) FROM cte2;

Anyone have any idea why this query doesn't work? Seems that if it is in the docs it should work; maybe a regression bug? I'm using H2 version 1.4.188.

Looks like H2 doesn't like that the value from the CTE isn't specified (e.g. cte1(FIRST_COLUMN)), but the docs seem to show that it isn't required. However, even if I fix that, it fails on the comma after the first CTE's closing parenthesis, which is exactly where I'm stuck with the query I'm trying to write.

score 0 · Answer 1 · answered Mar 30 '22 at 06:08

To write a recursive function to select the values you can use a code snippet as shown below.

WITH CTE1(COL1, COL2)
AS (
SELECT COL1 , COL2 FROM TABLE1 where COL2 = 'SOME_VALUE'
UNION ALL
SELECT TAB1.COL1, TAB1.COL2 from TABLE1 TAB1 JOIN CTE1 CT1 on CT1.COL2= TAB1.COL2
)
SELECT COL1, COL2 from CTE1 ORDER BY COL1;

Since this is quite a mess to understand, I will attach an example herewith to get a sound idea. Assume we have a table called UM_ORG which has 3 columns,

UM_ID
UM_NAME
UM_PARENT_ID

And now we are creating an organization structure as follows, ORG_A -> ORG_B -> ORG_C and ORG_A is the direct parent of ORG_B and ORG_B is the direct parent of ORG_C.

Now if we want to get all the organization ids that are under ORG_A we have to use a common table expression.

WITH childOrgs(UM_ID, UM_PARENT_ID)
AS (
SELECT UM_ID , UM_PARENT_ID FROM UM_ORG WHERE UM_PARENT_ID = 'ORG_A'
UNION ALL
SELECT UO.UM_ID, UO.UM_PARENT_ID FROM UM_ORG UO JOIN childOrgs CO ON CO.UM_ID= UO.UM_PARENT_ID
)
SELECT UM_ID, UM_PARENT_ID FROM childOrgs ORDER BY UM_ID;

With this you will get all the children under ORG_A

Recursive query with H2

1 Answers1