TREE VIEW IN ORACLE

-- SUMULATION OF TREE VIEW IN ORACLE
-- PARENT CHIELD RELATION IN ORACLE

CREATE TABLE stmenu (
id int,
Menuitem varchar2(75),
parentid int);

select * from stmenu


INSERT into stmenu values ('1','C:\Documents and Settings\All Users\Start Menu\Programs\','');
INSERT into stmenu values ('2','Startup\','1');
INSERT into stmenu values ('3','Administrative Tools\','1');
INSERT into stmenu values ('4','Accessories\','1');
INSERT into stmenu values ('5','System Tools\','4');
INSERT into stmenu values ('6','Internet Explorer.lnk','1');
INSERT into stmenu values ('7','Address Book.lnk','4');
INSERT into stmenu values ('8','Calculator.lnk','4');
INSERT into stmenu values ('9','Clock.lnk','4');
INSERT into stmenu values ('10','Entertainment','4');
INSERT into stmenu values ('11','Notepad.lnk','4');
INSERT into stmenu values ('12','Paint.lnk','4');
INSERT into stmenu values ('13','Synchronize.lnk','4');
INSERT into stmenu values ('14','Disk Cleanup.lnk','5');
INSERT into stmenu values ('15','Disk Defragmenter.lnk','5');
INSERT into stmenu values ('16','Files and Settings Transfer Wizard.lnk','5');
INSERT into stmenu values ('17','Scheduled Tasks.lnk','5');
INSERT into stmenu values ('18','Authorization Manager.lnk','3');
INSERT into stmenu values ('19','Certification Authority.lnk','3');
INSERT into stmenu values ('20','Cluster Administrator.lnk','3');
INSERT into stmenu values ('21','Component Services.lnk','3');
INSERT into stmenu values ('22','Computer Management.lnk','3');
INSERT into stmenu values ('23','Connection Manager Administration Kit.lnk','3');
INSERT into stmenu values ('24','Data Sources (ODBC).lnk','3');
INSERT into stmenu values ('25','DHCP.lnk','3');
INSERT into stmenu values ('26','Distributed File System.lnk','3');
INSERT into stmenu values ('27','DNS.lnk','3');
INSERT into stmenu values ('28','Event Viewer.lnk','3');
INSERT into stmenu values ('29','Group Policy Management.lnk','3');

select * from stmenu

---- TOTAL TREE VIEW START WITH THE PARENT NODE NULL

select (lpad(' ',10*(level-1)) || menuitem) MENU_ITEAM, parentid
from stmenu
start with parentid is null
connect by prior id=parentid

------ TOTAL TREE SHOWING PARENT NAME ALSO

select lpad(' ',10*(level-1)) || menuitem, parentid,
(select menuitem as parent_name from stmenu where id=x.parentid) parent_name
from stmenu x
start with parentid is null
connect by prior id=parentid

------ SHOWING ONLY 29th PARENT'S AND IT'S CHIELD

select lpad(' ',10*(level-1)) || menuitem, parentid,
(select menuitem as parent_name from stmenu where id=x.parentid) parent_name
from stmenu x
start with id=29
connect by prior parentid=id

--- if change the prior condition as this
--- AT PRIOR CONDITION, PARENTID=ID AND ID= PRANETID IS NOT SAME

select lpad(' ',10*(level-1)) || menuitem, parentid,
(select menuitem as parent_name from stmenu where id=x.parentid) parent_name
from stmenu x
start with id=29
connect by prior id=parentid

----- START WITH PARENT ID = 1 VIEW

SELECT
lpad(' ',5*(parentid-1))|| Menuitem
FROM stmenu
CONNECT BY PRIOR id=parentid
START WITH id=1;


select * from stmenu


---------- documentation ---
-- PRIOR - identifies the parent row in the column.
-- CONNECT BY PRIOR id=parentid will return different results to CONNECT BY PRIOR parentid=id
-- LEVEL - Returns a number indicating the level in the heirarchy: 1 for a root row, 2 for a child of a root, and so on.