Home » SQL & PL/SQL » SQL & PL/SQL » Connect by Prior
Connect by Prior [message #239720] Wed, 23 May 2007 03:28 Go to next message
mailtokkalyan
Messages: 65
Registered: December 2006
Location: Bangalore
Member

Hi all,
I am having employees table, in that some employess having Manager id. for this i have write a query to display in tree view. but how can i display the level of that employee.

for example,
Employees Table.....

Employee id last_name manager_id
100 King
101 Kochhar 100
102 Ram 101
103 Sundar 101
104 Raj 102
105 KKS 100
. . .
. . .
like that.

the Query...

SELECT LPAD(LAST_NAME,(LENGTH(LAST_NAME)+(LEVEL*2)-2),'_')
FROM EMPLOYEES
START WITH employee_id=100
connect by prior employee_id=manager_id;

the output is...
King
__Kochhar
____Ram
______Raj
____Sundar
__KKS

but, i am expecting the output like
1.King
1.1 Kochhar
1.1.1 Ram
1.1.1.1 Raj
1.1.2 Sundar
1.2 KKS

How can get like this???

Please help me.......
Thanks in advance..

Regards,
Kalyan
Re: Connect by Prior [message #239764 is a reply to message #239720] Wed, 23 May 2007 04:46 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You can achieve this through SYS_CONNECT_BY_PATH:

My script:
CREATE TABLE yourtable 
AS
SELECT 100 empid, 'King'    lname, cast(NULL AS NUMBER(3,0)) mgrid FROM dual UNION ALL
SELECT 101 empid, 'Kochhar' lname, 100  mgrid                      FROM dual UNION ALL
SELECT 102 empid, 'Ram'     lname, 101  mgrid                      FROM dual UNION ALL
SELECT 103 empid, 'Sundar'  lname, 101  mgrid                      FROM dual UNION ALL
SELECT 104 empid, 'Raj'     lname, 102  mgrid                      FROM dual UNION ALL
SELECT 105 empid, 'KKS'     lname, 100  mgrid                      FROM dual
/
col rn FORMAT A10

SELECT SUBSTR(sys_connect_by_path(rn,'.'),2) rn
     , empid
     , lname
     , mgrid
FROM ( SELECT rank() OVER ( partition by mgrid order by empid ) rn
            , empid
            , lname
            , mgrid
       FROM   yourtable
     )
CONNECT BY PRIOR empid = mgrid
START WITH       mgrid IS NULL
ORDER BY 1
/

DROP TABLE yourtable
/


My test run:
SQL> @orafaq

Table created.


RN              EMPID LNAME        MGRID
---------- ---------- ------- ----------
1                 100 King
1.1               101 Kochhar        100
1.1.1             102 Ram            101
1.1.1.1           104 Raj            102
1.1.2             103 Sundar         101
1.2               105 KKS            100

6 rows selected.


Table dropped.


MHE
Re: Connect by Prior [message #239772 is a reply to message #239764] Wed, 23 May 2007 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good one! ./fa/2115/0/

Regards
Michel
Re: Connect by Prior [message #239773 is a reply to message #239764] Wed, 23 May 2007 04:55 Go to previous message
mailtokkalyan
Messages: 65
Registered: December 2006
Location: Bangalore
Member

Hi,
Very very thanks...
Regards,
Kalyan
Previous Topic: next_day function help
Next Topic: not urgnet at all - ORA-01455: converting column overflows integer datatype
Goto Forum:
  


Current Time: Thu Dec 08 14:34:38 CST 2016

Total time taken to generate the page: 0.16022 seconds