OraFAQ Forum: SQL & PL/SQL » recursive query

Home » SQL & PL/SQL » SQL & PL/SQL » recursive query (12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

recursive query [message #668064]

Sat, 03 February 2018 05:15

m.abdulhaq
Messages: 253
Registered: April 2013
Location: Ajman

Senior Member

I have a hierarchical table containing all the parent and child objects , i want to display all of them in one.


create table machine_mast(mch_code varchar2(12),mch_name varchar2(200),parent_mch_code varchar2(12),mch_level varchar2(12));

insert into machine_mast(mch_code,mch_name,parent_mch_code ,mch_level ) values ('1','machine1',null,'level1');

insert into machine_mast(mch_code,mch_name,parent_mch_code ,mch_level ) values ('11','machine2','1','level11');

insert into machine_mast(mch_code,mch_name,parent_mch_code ,mch_level ) values ('111','machine3','11','level111');


1	machine1		level1
11	machine2	1	level11
111	machine3	11	level111


--i want to see all the levels in one line,


machine 1,machine2,machine3

Report message to a moderator

Re: recursive query [message #668065 is a reply to message #668064]

Sat, 03 February 2018 06:28

Michel Cadot
Messages: 68508
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SYS_CONNECT_BY_PATH

Report message to a moderator

Re: recursive query [message #668067 is a reply to message #668065]

Sat, 03 February 2018 07:54

m.abdulhaq
Messages: 253
Registered: April 2013
Location: Ajman

Senior Member

Thanks Michael, i need to filter the level 2 of any given machine code, for example i may pass level 3 or level 1 in where clause but it should return only level 2.


SELECT ListAgg(mch_code,',') 
       within group(order by Level desc) as revPath
FROM machine_mast
START WITH mch_code = '111'
CONNECT BY PRIOR parent_mch_code = mch_code;

-- I WILL BE GETTING

1,11,111


SELECT ListAgg(mch_code,',') 
       within group(order by Level desc) as revPath
FROM machine_mast
START WITH mch_code = '11'
CONNECT BY PRIOR parent_mch_code = mch_code;

1,11

--I Want only level 11.

Report message to a moderator

Re: recursive query [message #668068 is a reply to message #668067]

Sat, 03 February 2018 08:30

Michel Cadot
Messages: 68508
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

WHERE LEVEL=2

Report message to a moderator

Re: recursive query [message #668069 is a reply to message #668068]

Sat, 03 February 2018 09:13

m.abdulhaq
Messages: 253
Registered: April 2013
Location: Ajman

Senior Member

thanks michael, i used the following, i want to search machine recursively , if i put mch_code of any level it must give me the mch connected to level 2 only.


SELECT 
SUBSTR (LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(mch_code, '/'), ( INSTR(LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(mch_code, '/'),'/',-1,1)+1)   ) A,
 INSTR(LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(mch_code, '/'),'/',-1,1)+1, 
  LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(mch_code, '/') "Path"
   FROM machine_mast
   where level =2
   START WITH mch_code = '1'
   CONNECT BY PRIOR mch_code = parent_mch_code

---the below query fails
SELECT 
SUBSTR (LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(mch_code, '/'), ( INSTR(LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(mch_code, '/'),'/',-1,1)+1)   ) A,
 INSTR(LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(mch_code, '/'),'/',-1,1)+1, 
  LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(mch_code, '/') "Path"
   FROM machine_mast
   where level =2
   START WITH mch_code = '111'
   CONNECT BY PRIOR mch_code = parent_mch_code

[Updated on: Sat, 03 February 2018 09:31]

Report message to a moderator

Re: recursive query [message #668070 is a reply to message #668069]

Sat, 03 February 2018 10:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>--i want to see all the levels in one line,
Is above or below correct?
>--I Want only level 11.

What results need to be produce?
SHOW us & don't tell us.

Report message to a moderator

Re: recursive query [message #668071 is a reply to message #668069]

Sat, 03 February 2018 10:23

Michel Cadot
Messages: 68508
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You have 3 posts and 3 different requirements.
The first one is answered by my first post.
The second one is answered by my second post (which is independent of and doesn't need the first one).
The third one will be answered when you will answer BlackSwan's question.

Note that "level 2" is meaningless if you don't say from what it is level 2.
Post a more complete and complex test case and gives the result for different inputs.

[Updated on: Sat, 03 February 2018 10:26]

Report message to a moderator

Re: recursive query [message #668072 is a reply to message #668070]

Sat, 03 February 2018 10:30

m.abdulhaq
Messages: 253
Registered: April 2013
Location: Ajman

Senior Member

i am sorry , the results i need .


create table machine_mast(mch_code varchar2(12),mch_name varchar2(200),parent_mch_code varchar2(12),mch_level varchar2(12));

insert into machine_mast(mch_code,mch_name,parent_mch_code ,mch_level ) values ('1','machine1',null,'level1');

insert into machine_mast(mch_code,mch_name,parent_mch_code ,mch_level ) values ('11','machine2','1','level11');

insert into machine_mast(mch_code,mch_name,parent_mch_code ,mch_level ) values ('111','machine3','11','level111');

insert into machine_mast(mch_code,mch_name,parent_mch_code ,mch_level ) values ('2','machinex',null,'level1');

insert into machine_mast(mch_code,mch_name,parent_mch_code ,mch_level ) values ('22','machinexx','2','level11');

insert into machine_mast(mch_code,mch_name,parent_mch_code ,mch_level ) values ('222','machinexxx','22','level111');

insert into machine_mast(mch_code,mch_name,parent_mch_code ,mch_level ) values ('2222','machinexxx','222','level1111');



--- what i want is

select mch_code from machine_mast
where mch_level= 'level11'
and mch_code = @can_be_any_mch_code


---if user passes mch_code = '2222'
--it should return it level 2 mch_code or 2 parent of hierarchy = '22'

--if the user passes mch_code = '111' 
--it should return it level 2 mch_code or 2 parent of hierarchy = '11'

--it is quite tricky.

Report message to a moderator