Topic: SQL*Plus and PL/SQL >> Connect by prior
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Rush |
Posted: Dec 13, 2007 10:49:55 PM |
Total Post: 127
Joined: Jan, 2007
|
Hi Taj and all
My query is to show employee of emp table by level.
i.g-
Employee
-----------
king
smith
Scott
abc
.....so on
Thanks
Rupesh |
|
|
 Nehru |
| Posted: Dec 14, 2007 12:04:10 AM |  |
|
Total Post: 46
Joined: Jul, 2007
|
Hi,
I Know something about this Connet By Prior let me know to you.
If you have our standard table "EMP" then try the following Query then you can understand about this.
The start with .. connect by clause can be used to select data that has a hierarchical relationship
(usually some sort of parent->child (boss->employee or thing->parts).
http://www.adp-gmbh.ch/ora/sql/connect_by.html -- Follow this link you can under stand clearly.
SELECT EMPNO,ENAME, MGR,DEPTNO FROM EMP
CONNECT BY PRIOR EMPNO = MGR START WITH MGR IS NULL
OR
select lpad(' ',2*(level-1)) || to_char(EMPNO) Relation,ename,mgr,deptno
from EMP
start with MGR is null
connect by prior EMPNO = MGR;
The Result like it can start with MGR is null ( He is the Boss) it can go hierarical level wise
just like Boss --> Subordinate....
Any one Let me know if am wrong.
Take Care,
Nehru
|
|
|
|
|
| Rush |
| Posted: Dec 14, 2007 05:52:04 AM | |
|
Total Post: 127
Joined: Jan, 2007
|
Thank a lot Nehru :)
this is the query which was needed.
it's working but i have to add level number with it,which i will do.
Thanks again
RUPesh
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 387 |
| Vinoth Kumar | 379 |
| Gopu Gopi | 350 |
| Gitesh Trivedi | 322 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
