Answer Posted / gvmahesh
NOCYCLE keyword is default in sequences.
CYCLE :If we want use cycle keyword first we check the table
having primary key or not.If there is no primary key we
absolutely use the CYCLE keyword.
In hierarchical structure,the descendant act as a
ancestor,is called CYCLE.
For eliminating ancestor descendant problem we use NOCYCLE
KEYWORD.
for example we take the EMP table
In EMP table the MGR column has a NULL managerial number.In
that column we insert some of the empno.Then ancestor
descendant problem occur.
select * from emp;
update emp set mgr=7566 where mgr is null;
Then we write a hierarchical query
select empno,ename,sal,mgr from emp
start with ename='KING'
connect by prior empno=mgr;
we execute this query it shows an error
ERROR:
ORA-01436: CONNECT BY loop in user data.
in this situation also we want to see the data through
NOCYCLE keyword.
select empno,ename,sal,mgr from emp
start with ename='KING'
connect by NOCYCLE prior empno=mgr;
| Is This Answer Correct ? | 3 Yes | 0 No |
Post New Answer View All Answers
what are the maximum number of rows that can be constructed by inserting rows directly in value list? : Transact sql
What is the difference between alter trigger and drop trigger statements?
tell me about various levels of constraint. : Sql dba
Explain the difference between 'between' & 'and' operators in sql
How do you clear the screen in sql?
Can two tables have same primary key?
What is the difference between functions, procedures, and packages in pl/sql?
Is join and inner join the same?
How many primary keys can a table have?
What are pl/sql cursor exceptions?
What are the types of join in sql?
Which are the different character-manipulation functions in sql?
Can we insert in sql function?
What is the difference between sum and count in sql?
how to get @@error and @@rowcount at the same time? : Sql dba
