Home » SQL & PL/SQL » SQL & PL/SQL » recursive query (12c)
recursive query [message #668064] Sat, 03 February 2018 05:15 Go to next message
m.abdulhaq
Messages: 243
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







Re: recursive query [message #668065 is a reply to message #668064] Sat, 03 February 2018 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 65959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SYS_CONNECT_BY_PATH

Re: recursive query [message #668067 is a reply to message #668065] Sat, 03 February 2018 07:54 Go to previous messageGo to next message
m.abdulhaq
Messages: 243
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.




Re: recursive query [message #668068 is a reply to message #668067] Sat, 03 February 2018 08:30 Go to previous messageGo to next message
Michel Cadot
Messages: 65959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

WHERE LEVEL=2

Re: recursive query [message #668069 is a reply to message #668068] Sat, 03 February 2018 09:13 Go to previous messageGo to next message
m.abdulhaq
Messages: 243
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 Go to previous messageGo to next message
BlackSwan
Messages: 26274
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.
Re: recursive query [message #668071 is a reply to message #668069] Sat, 03 February 2018 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 65959
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 Go to previous messageGo to next message
m.abdulhaq
Messages: 243
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.






Re: recursive query [message #668073 is a reply to message #668072] Sat, 03 February 2018 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 65959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have only 2 main branches, post with several sub-branches.

Quote:
--it is quite tricky.
No it is not as soon as you have clear requirements for ALL possible cases.

Re: recursive query [message #668074 is a reply to message #668073] Sat, 03 February 2018 12:38 Go to previous messageGo to next message
m.abdulhaq
Messages: 243
Registered: April 2013
Location: Ajman
Senior Member
i am sorry michal and blackswan. this is the data i have .
Re: recursive query [message #668075 is a reply to message #668074] Sat, 03 February 2018 12:53 Go to previous messageGo to next message
Michel Cadot
Messages: 65959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you can't imagine some rows with 121, 212, 221, 13, 131, 112, 1121...?

So write the rows for this tree:

/forum/fa/13745/0/
  • Attachment: tree.jpg
    (Size: 43.37KB, Downloaded 249 times)
Re: recursive query [message #668076 is a reply to message #668074] Sat, 03 February 2018 12:55 Go to previous message
BlackSwan
Messages: 26274
Registered: January 2009
Location: SoCal
Senior Member
m.abdulhaq wrote on Sat, 03 February 2018 10:38
i am sorry michal and blackswan. this is the data i have .
https://community.oracle.com/thread/4118773
Previous Topic: dba_dependencies_columns
Next Topic: difference between date
Goto Forum:
  


Current Time: Wed Nov 14 11:33:32 CST 2018