Same Conect By query works fine in 9i but not in 10g - Please help [message #394553] |
Fri, 27 March 2009 14:30  |
cts_srividya
Messages: 2 Registered: March 2009
|
Junior Member |
|
|
We recently moved our project from 9i to 10g. A connect by query works fine in 9i environment but same not working in 10g environment. Please help.
While trying with a sample table, that also behaves samem way. Please find the query and help on how to solve.
create table test_connect_by (
parent number,
child number,
constraint uq_tcb unique (child)
);
insert into test_connect_by values ( 5, 2);
insert into test_connect_by values ( 5, 3);
insert into test_connect_by values (18,11);
insert into test_connect_by values (18, 7);
insert into test_connect_by values (17, 9);
insert into test_connect_by values (17, 8);
insert into test_connect_by values (26,13);
insert into test_connect_by values (26, 1);
insert into test_connect_by values (26,12);
insert into test_connect_by values (15,10);
insert into test_connect_by values (15, 5);
insert into test_connect_by values (38,15);
insert into test_connect_by values (38,17);
insert into test_connect_by values (38, 6);
insert into test_connect_by values (null, 38);
insert into test_connect_by values (null, 26);
insert into test_connect_by values (null, 18);
COMMIT;
In Oracle 10g:
SQL> select level, lpad(' ',2*(level-1)) || to_char(child) s
2 from test_connect_by
3 start with level = 1
4 connect by prior child = parent;
no rows selected
In Oracle 9i:
SQL> select level, lpad(' ',2*(level-1)) || to_char(child) s
2 from test_connect_by
3 start with level = 1
4 connect by prior child = parent;
LEVEL S
--------------------------------------------------------------------------------
---------------
1 2
1 3
1 10
1 5
2 2
2 3
1 9
1 8
1 11
1 7
1 13
1 1
1 12
1 15
2 10
2 5
3 2
3 3
1 17
2 9
2 8
1 6
1 38
2 15
3 10
3 5
4 2
4 3
2 17
3 9
3 8
2 6
1 26
2 13
2 1
2 12
1 18
2 11
2 7
39 rows selected
[Updated on: Fri, 27 March 2009 15:11] by Moderator Report message to a moderator
|
|
|
|
|
|
Re: Same Conect By query works fine in 9i but not in 10g - Please help [message #394603 is a reply to message #394553] |
Sat, 28 March 2009 06:19   |
 |
delna.sexy
Messages: 941 Registered: December 2008 Location: Surat, The Diamond City
|
Senior Member |
|
|
Well, your query is not working.
There is some problem, and I am not able to solve it.
SQL>ed
Wrote file afiedt.buf
1 select lpad(' ',2*(level-1)) || to_char(child) s
2 from test_connect_by
3 start with level = 1
4* connect by prior child = parent
SQL>/
from test_connect_by
*
ERROR at line 2:
ORA-01788: CONNECT BY clause required in this query block
SQL>select * from v$version;
BANNER
================================================================
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
5 rows selected.
I am requesting our GURUS to solve the issue.
regards,
Delna
[Updated on: Sat, 28 March 2009 06:23] Report message to a moderator
|
|
|
|
|
|