Repeat action like with recursive

Support
1

Greetings,

I try to replace recursive query done in SQLite with repeat actions.
I’m missing something but I don’t succeed to get the same result.

the test dataset is
SLAVE_ID
MASTER_ID
SLAVE_CAT : an ID can be SLAVE ONLY, BOTH or MASTER ONLY
MASTER_CAT : an ID can be BOTH or MASTER ONLY
the PATH is the concatenation of the IDs
the DEPTH_TREE is the DEPTH of the PATH so it should be the number of iteration - 1

question :

  • I don’t succeed to specify simply conditional root points, in my case the ID with the attribute ‘SLAVE ONLY’ in SLAVE_CAT
  • I don’t succeed to fully explore the hierarchy to get the upper PATH_ID,
    if I use sc.PATH_ID || ‘>’ || c.SLAVE_ID I never get the last path,
    if I use sc.PATH_ID || ‘>’ || c.MASTER_ID then I can’t get the root path with only the SLAVE_ID

The repeat action seems very powerful as it give the possibility to make recursive outer join (never seen that anywhere else) but it is not that intuitive at first look.

In the repeat loop, is there is a system variable that sends back the current iteration number ? This way we could handle exception in the first iteration.

the structure of the query is

WITH RECURSIVE temp_table AS
(
SELECT DISTINCT
SLAVE_ID AS INIT_ID,
SLAVE_ID,
MASTER_ID,
SLAVE_CAT AS INIT_CAT,
SLAVE_CAT,
MASTER_CAT,
0 AS DEPTH_TREE,
CAST ( SLAVE_ID as text) AS PATH_ID
FROM recurtest
WHERE SLAVE_CAT = ‘SLAVE ONLY’
UNION
SELECT DISTINCT
sc.INIT_ID AS INIT_ID,
c.SLAVE_ID AS SLAVE_ID,
c.MASTER_ID AS MASTER_ID,
sc.INIT_CAT AS INIT_CAT,
c.SLAVE_CAT AS SLAVE_CAT,
c.MASTER_CAT AS MASTER_CAT,
sc.DEPTH_TREE + 1 AS DEPTH_TREE,
sc.PATH_ID || ‘>’ || c.SLAVE_ID AS PATH_ID
FROM
temp_table AS sc
JOIN
recurtest AS c
ON
sc.MASTER_ID = c.SLAVE_ID
)
SELECT DISTINCT
INIT_ID,
SLAVE_ID,
MASTER_ID,
INIT_CAT,
SLAVE_CAT,
MASTER_CAT,
DEPTH_TREE,
PATH_ID
FROM
temp_table;

Regards

extract closure.zip (3.9 KB)

2

Hi Christophe,

I’m not familiar with recursive SQL, can’t comment on that.

A convenient way to debug a recursive “Repeat” iteration is to populate the “Input” action of the iterated module. In the 1st iteration, it’s the input dataset of the “Repeat” action itself.

To debug 2nd and further iterations, send the result table of the iterated module into the “Input” action of the same module. Go to the last action in the result table, right-click it and choose “Send to module”. In this case, you can walk each recursive iteration one by one while clearly seeing what’s in the input and what’s in the output of the iterated module.

3

the debugging mode step by step is impressive.
I understood my issue,
hard to catch the subtilities of ‘Repeat’ vs ‘With recursive’ :
in EM, when we select ‘Append’ option, the append is done after all the iterations are done. It’s a kind of Traversal Search.
In a recursive query, the ‘appended’ table is reinjected on each iteration, it’s a Breadth-first search.
So I have to make the append in the loop and select ‘Return last not empty result’ if I want the Repeat to behave like With recursive

Regards

1 Like
4

Do you mind posting the updated example?

5

I understood my mistake, I didn’t corrected it yet…
I have a few other tests to proceed : I’ll post the interesting ones.

6

EM repeat with table.zip (6.1 KB)
Hi,

Here is an example of ‘with recursive’ SQL behavior

Regards