General
Enthusiast

## recursivity

Hello,

I would like to know if there is a recursive query to do the following.

ORIGIN TABLE

SON                       FATHER

----------                  ----------

G1                            G2

G2                            G3

G3                            G5

G4                            G6

G5                            null

RESULT SHOULD BE, for example the son G1 has G2 has father and at his turn G2 has G3 as father,

Thus , G1 should have G2 and G3 as fathers.

The final result should be :

SON                       FATHER

----------                  ----------

G1                            G2

G1                            G3

G1                            G5

G2                            G3

G2                            G5

G3                            G5

G4                            G3

G4                            G5

G5                             null

5 REPLIES
Supporter

## Re: recursivity

I am not sure that G4 is reflected correctly in your result set - or I did not understand your example correctly. Can you validate please.

Would also be nice if you share a ddl and some insert into statements to set up your test data - as it will make it easier to write a SQL.

Enthusiast

## Re: recursivity

ORIGIN TABLE

SON                       FATHER

----------                  ----------

G1                            G2

G2                            G3

G3                            G5

G4                            G5

G5                            null

RESULT SHOULD BE, for example the son G1 has G2 has father and at his turn G2 has G3 as father,

Thus , G1 should have G2 and G3 as fathers.

The final result should be :

SON                       FATHER

----------                  ----------

G1                            G2

G1                            G3

G1                            G5

G2                            G3

G2                            G5

G3                            G5

G4                            G5

G5                             null

CREATE MULTISET TABLE kidsL.RECURSIVIDAD ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

COD_HIJO CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,

COD_PADRE CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX ( COD_HIJO );

Supporter

## Re: recursivity

`WITH RECURSIVE base (cod_hijo, cod_padre,ctn)as(select cod_hijo,        cod_padre,        cast(1 as smallint)from RECURSIVIDADwhere cod_padre <> ''union allselect b.cod_hijo,       c.cod_padre,       b.ctn + 1from RECURSIVIDAD c     join     base b        on b.cod_padre = c.cod_hijo)select *from basewhere ctn < 100--and cod_padre <> ''order by 1,2,3;`

I am not sure why '' is only once in the result set.

Seems a bit inconsitent to me.

So either remove it always or keep it always. So uncomment the cod_padre <> '' or keep the comment.

If the example is really your final requirement you need to add a more complicated condition with an correlated subquery to your source table.

Supporter

## Re: recursivity

P.S. I added the ctn condition in case you have loops in your graph - which would end up in an endless loop. So check your output. If it contains ctn = 100 you need either increase the filter condition or you need to fix the graph.

Enthusiast

## Re: recursivity

perfect, thank you