What is connect by prior?

[jeevalokesh]

hi
i need answer for one question


what is connect by priority? which trigger can be used for this ?

[debasisdas]

Connect by priority ?

What is that ??

Is it in Oracle ???

[debasisdas]

If you are talking of CONNECT BY PRIOR
then please post back
I may help you.....

[jeevalokesh]

ok explain it


one favour ,

im jeeva im proficient in oracle developer
i like to do project in forms & reports or pl/sql
can u help me
plz give some topic

[debasisdas]

USING ANSI SQL --TO DISPLAY HIERARCHICAL DATA
=============================================
The START WITH...CONNECT BY clause.
The PRIOR operator.
The LEVEL pseudocolumn.

SYNTAX
------------------------
[[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.

[jeevalokesh]

Thank u very much

[debasisdas]

Try these sample code.

 
SELECT ENAME,EMPNO,MGR FROM EMP START WITH MGR IS NULL CONNECT BY PRIOR EMPNO=MGR
------------
SELECT ENAME,EMPNO,MGR FROM EMP START WITH MGR IS NULL CONNECT BY MGR=PRIOR EMPNO;
------------
SELECT ENAME,EMPNO,MGR FROM EMP START WITH ENAME='JONES' CONNECT BY MGR=PRIOR EMPNO;
-------------
SELECT ENAME,EMPNO,MGR FROM EMP START WITH HIREDATE=(SELECT MIN(HIREDATE) FROM EMP) CONNECT BY MGR=PRIOR EMPNO;
 

Hope these will help u a lot.

[jeevalokesh]

Thank u very much it is very useful to me