On Dec 29, 12:57 am, weird0 <amiredi...@gmail.com> wrote:
> When I run the following hierarchial query , I receive the following
> exception.
>
> Can anyone tell me how to remove this exception and why is it
> appearing? Is it some limitation of the Oracle version?
>
> select l.acct_type,l.acct_no,l.create_dt,l.contract_dt
> from ln_display_mis l
> start with l.acct_type='MTG'
> CONNECT BY PRIOR l.create_dt=l.contract_dt;
No one can tell you why without a table definition, sample data and
the Oracle release you're using (all four or five numbers). However,
you have at least one pair of records which reference each other as a
'parent', thus the ORA-01436 error:
SQL> select empno, mgr
2 from emp;
EMPNO MGR
---------- ----------
8813 8815 <---------
8815 8813 <--------- These
records reference each other and create a loop
7369 7902
7499 7698
7521 7698
7566 7839
7654 7698
7698 7839
7782 7839
7788 7566
7839
EMPNO MGR
---------- ----------
7844 7698
7876 7788
7900 7698
7902 7566
7934 7782
16 rows selected.
SQL> select mgr, empno, ename, job
2 from emp
3 connect by mgr = prior empno
4 /
MGR EMPNO ENAME JOB
---------- ---------- ---------- ---------
7566 7788 SCOTT ANALYST
7788 7876 ADAMS CLERK
7566 7902 FORD ANALYST
7902 7369 SMITH CLERK
7698 7499 ALLEN SALESMAN
7698 7521 WARD SALESMAN
7698 7654 MARTIN SALESMAN
7698 7844 TURNER SALESMAN
7698 7900 JAMES CLERK
7782 7934 MILLER CLERK
7788 7876 ADAMS CLERK
MGR EMPNO ENAME JOB
---------- ---------- ---------- ---------
7839 7566 JONES MANAGER
7566 7788 SCOTT ANALYST
7788 7876 ADAMS CLERK
7566 7902 FORD ANALYST
ERROR:
ORA-01436: CONNECT BY loop in user data
<------- which results in this
15 rows selected.
SQL>
And if you modify the query to start with a given condition then the
looping records aren't displayed as Oracle will 'avoid' those rows in
the output:
SQL> select mgr, empno, ename, job
2 from emp
3 connect by mgr = prior empno
4 start with mgr is null;
MGR EMPNO ENAME JOB
---------- ---------- ---------- ---------
7839 KING PRESIDENT
7839 7566 JONES MANAGER
7566 7788 SCOTT ANALYST
7788 7876 ADAMS CLERK
7566 7902 FORD ANALYST
7902 7369 SMITH CLERK
7839 7698 BLAKE MANAGER
7698 7499 ALLEN SALESMAN
7698 7521 WARD SALESMAN
7698 7654 MARTIN SALESMAN
7698 7844 TURNER SALESMAN
MGR EMPNO ENAME JOB
---------- ---------- ---------- ---------
7698 7900 JAMES CLERK
7839 7782 CLARK MANAGER
7782 7934 MILLER CLERK
14 rows selected.
SQL>
Of course you CAN cause Oracle to report the same error with the
proper starting condition:
SQL> select mgr, empno, ename, job
2 from emp
3 connect by mgr = prior empno
4 start with empno = 8813;
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
SQL>
You need to check your data.
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
