asked on

how to write a recursive logic in oracle

1)2752088 is the parent record of type 'product' and under that product, children's are 'fraggg111'[2752089],'fraggggs'[2752090],'supportmaterials'[2752091],'4444.xls'[2752133],'1111'[2752092].

2)So first 2752088 value should be passed into the parent table and its of type 'product' and then look into the child_assoc table and take the corresponding child_id i.e.2752089 and look into the parent table to check the type is 'frag' and so ignore the record.

3)again 2752089 having some children's in child_assoc table and take the child_id's i.e.2752167, 2752172 and look into the parent table to check their types, now in this case ,type is 'assets' and so pick up the records;

the final output would be 2752167,2752172,2752316,2752133,2752094 records should be returned from parent table.

create table child_assoc(id,parent_id,child_id,filename)

id-primary key
parent_id---->references id in parent table
child_id---->references id in parent table

insert into child_assoc values (1,2752088,2752089,'fraggg111')

insert into child_assoc values(2,2752089,2752167,'ram.zip')
insert into child_assoc values(3,2752089,2752172,'shyam.zip')

insert into child_assoc values(4,2752088,2752090,'fraggggs')

insert into child_assoc values(5,2752088,2752091,'supportmaterials')
insert into child_assoc values(6,2752091,2752316,'33.zip')

insert into child_assoc values(7,2752088,2752133,'4444.xls')

insert into child_assoc values(8,2752088,2752092,'1111')
insert into child_assoc values(9,2752092,2752093,'22222')
insert into child_assoc values(10,2752093,2752094,'2222.zip')


create table parent(id,uuid,type)

insert into parent values(2752088,'wrwrwr','product')

insert into parent values(2752089,'vmbm','frag')
insert into parent values(2752167,'vnvn','assets')
insert into parent values(2752172,'sssfsf','assets')

insert into parent values(2752090,'2424','frag')

insert into parent values(2752091,'fhfhf','fold')
insert into parent values(2752316,'ippip','assets')

insert into parent values(2752133,'gjgjgj','assets')

insert into parent values(2752092,'sfsfsf','frag')
insert into parent values(2752093,'hjasdad','frag')
insert into parent values(2752094,'ghkhk','assets')

Open in new window

Pawan Kumar

Pls try like this -

SELECT id,parent_id,child_id,filename
FROM yourtable
CONNECT BY PRIOR id = parent_id

http://docs.oracle.com/database/122/SQLRF/Hierarchical-Queries.htm#SQLRF52335

Pawan Kumar

SELECT id,parent_id,child_id,filename
   FROM yourtable
   CONNECT BY PRIOR child_id = parent_id

Open in new window

chaituu

ASKER

pavan,

Thanks for the reply.in your query we are doing recursive logic in child_assoc table that is correct and also in parallel need to look into parent table to check the type of the child_id to see whether their type is 'frag'/'fold'/'assets'.we need to retrieve those records which are of type 'assets';

Pawan Kumar

For that add a where clause.

chaituu

ASKER

how to pass input id i.e. parent_id=2752088 in where clause?

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

Without using common table expression -
select c.* from
(SELECT id,parent_id,child_id,filename
FROM child_assoc
CONNECT BY PRIOR id = parent_id
) c
where c.child_id in (select id from parent where type = 'assets');