Connect by In Oracle

[dilippanda]

Hi,

Can anyone please guide me the use of connect by clause with one example?

Awaiting for your reply.

Thanks,
Dilip

[debasisdas]

USING ANSI SQL
=================
The START WITH...CONNECT BY clause.
The PRIOR operator.
The LEVEL pseudocolumn.

SYN
----
[[START WITH condition 1]CONNECT BY condition2]

START WITH condition 1
-----------------------
IT SPECIFIES THE ROOT OF THE HIERARCHY. ALL ROWS THAT SATISFIES CONDITION1 ARE CONSIDERED ROOT ROWS.IF START WITH CLAUSE IS NOT SPECIFIED ALL ROWS ARE CONSIDERED ROOT ROWS.CONDITION 1 CAN INCLUDE A SUB-QUERY.

CONNECT BY condition2
---------------------
SPECIFIES THE RELATIONSHIP BETWEEN PARENT ROWS AND CHILD ROWS.THE RELATIONSHIP IS EXPRESSED AS A COMPARISION EXPRESSION,WHERE COLUMNS FROM THE CURRENT ROWS ARE COMPARED TO CORRESPONDING PARENT COLUMNS.THE CONDITION2 MUST CONTAIN THE PRIOR OPERATOR WHICH IS USED TO IDENTIFY COLUMNS FROM THE PARENT ROWS.CONDITION2 CAN'T CONTAIN ANY SUB-QUERY.
SINCE THE CONNECT BY CONDITION SPECIFIES THE PARENT-CHILD RELATIONSHIP IT CAN'T CONTAIN A LOOP(IF A ROW IS BOTH DIRECT ANCESTOR AND DIRECT DECENDANT OF ANOTHER ROW THERE IS A LOOP).

PRIOR
------
PRIOR IS A BUILT-IN ORACLE SQL OPERATOR THAT IS USED WITH THE HIERARCHICAL QUERIES ONLY. IN A HIERARCHICAL QUERY THE CONNECT BY CLAUSE SPECIFIES THE RELATION BETWEEN PARENT AND CHILD ROWS.WHEN THE PRIOR OPERATOR IS USED IN AN EXPRESSION IN THE CONNECT BY CONDITION, THE EXPRESSION FOLLOWING THE PRIOR KEYWORD IS EVALUATED FOR THE PARENT ROW OF THE CURRENT ROW IN THE QUERY.

[debasisdas]

select Ename,empno,mgr From Emp Start With Mgr Is Null Connect By Prior Empno=mgr