recursivity

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 );


insert into kidsL.RECURSIVIDAD values ('G1','G2')


insert into kidsL.RECURSIVIDAD values ('G2','G3')


insert into kidsL.RECURSIVIDAD values ('G3','G5')


insert into kidsL.RECURSIVIDAD values ('G4','G5')


insert into kidsL.RECURSIVIDAD values ('G5','')


Supporter

Re: recursivity

WITH RECURSIVE base (cod_hijo, cod_padre,ctn)
as
(
select cod_hijo,
cod_padre,
cast(1 as smallint)
from RECURSIVIDAD
where cod_padre <> ''
union all
select b.cod_hijo,
c.cod_padre,
b.ctn + 1
from RECURSIVIDAD c
join
base b
on b.cod_padre = c.cod_hijo
)
select *
from base
where 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