| Oracles recursive Connect By [message #633016] |
Tue, 10 February 2015 03:59  |
 |
ksetty
Messages: 3
Registered: February 2015
|
Junior Member |
|
|
Hi everyone,
I am working with Oracles recursive capabilities Connect By. I have the following data (simple example):
create table edge (from_node integer, to_node integer, status);
INSERT INTO edge VALUES (1 , 5 , 'A')
INSERT INTO edge VALUES (2, 5, 'A' )
INSERT INTO edge VALUES (3, 5, 'A' )
INSERT INTO edge VALUES (4, 5, 'A')
INSERT INTO edge VALUES (6, 1, 'A')
I use the following query:
SELECT 5 as start_node, to_node AS end_node, LEVEL as Depth
FROM edge
where status 'A'
start with to_node=5
connect by nocycle prior to_node = from_node and status 'A' and and LEVEL <= 2;
Ok, I would expect the following data to show up (which they do)
start_node | to_node | Depth
--------------------------------------
5 | 1 | 1
5 | 2 | 1
5 | 3 | 1
5 | 4 | 1
5 | 6 | 2
I update status to "I" for first rowr, i.e.
Update edge set status 'I' where from_node=1
and I execute again the same query but result I get is as below, where first row is removed but last row is also seen which I don't want, as it's parent itself doesn't exist now in the result
start_node | to_node | Depth
--------------------------------------
5 | 2 | 1
5 | 3 | 1
5 | 4 | 1
5 | 6 | 2<<<<<
Please let me know what is that I am missing here
Regards,
Krishna
[Code tags added]
[Updated on: Tue, 10 February 2015 04:01] by Moderator Report message to a moderator |
|
|
|
| Re: Oracles recursive Connect By [message #633019 is a reply to message #633016] |
Tue, 10 February 2015 04:09  |
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Welcome to the forum.
Please read and follow How to use [code] tags and make your code easier to read?
Your test case has numerous errors:
1) missing data type in create table
2) missing semi-colons on the inserts (add them and we can copy and paste straight into sqlplus
3) missing column list on the inserts - as it stands to_node is getting values 1 and 5 - which doesn't give the results you say you're getting
4) missing = signs in the select statement.
5) Extra AND in select statement.
7) Missing = in update statement.
Fix all that and we'll take a look.
|
|
|
|
| Re: Oracles recursive Connect By [message #633025 is a reply to message #633019] |
Tue, 10 February 2015 05:02 |
|
ksetty
Messages: 3
Registered: February 2015
|
Junior Member |
|
|
Hi everyone,
I am working with Oracles recursive capabilities Connect By and discovered, that cycle-free is not quite true. I have the following data (simple example):
create table edge (from_node integer, to_node integer, status char);
INSERT INTO edge VALUES (1 ,5 , 'A');
INSERT INTO edge VALUES (2, 5, 'A' );
INSERT INTO edge VALUES (3, 5, 'A' );
INSERT INTO edge VALUES (4, 5, 'A');
INSERT INTO edge VALUES (6, 1, 'A');
I use the following query:
SELECT 5 as start_node, to_node AS end_node, LEVEL as Depth
FROM edge
where status 'A'
start with to_node=5
connect by nocycle prior to_node = from_node and status 'A' and LEVEL <= 2;
Ok, I would expect the following data to show up (which they do)
start_node | to_node | Depth
--------------------------------------
5 | 1 | 1
5 | 2 | 1
5 | 3 | 1
5 | 4 | 1
5 | 6 | 2
I update status = "I" for first row, i.e.
Update edge set status 'I' where from_node=1;
and I execute again the same query but result I get is as below, where first row is removed but last row is also seen which I dont want to as its parent itself doesn;t exist now in the result
start_node | to_node | Depth
--------------------------------------
5 | 2 | 1
5 | 3 | 1
5 | 4 | 1
5 | 6 | 2
Please let me know what is that I am missing here
|
|
|
|
| Re: Oracles recursive Connect By [message #633026 is a reply to message #633025] |
Tue, 10 February 2015 05:09 |
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You're not off to a good start here. I pointed out 6 issues and asked you to use code tags.
You've fixed issues 1, 2 and 5.
You need to fix the other three and start using code tags (read the link I posted above).
|
|
|
|
| Re: Oracles recursive Connect By [message #633049 is a reply to message #633016] |
Tue, 10 February 2015 07:43 |
Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
ksetty wrote on Tue, 10 February 2015 04:59Ok, I would expect the following data to show up (which they do)
start_node | to_node | Depth
--------------------------------------
5 | 1 | 1
5 | 2 | 1
5 | 3 | 1
5 | 4 | 1
5 | 6 | 2
Really?
SQL> select * from edge;
FROM_NODE TO_NODE S
---------- ---------- -
1 5 A
2 5 A
3 5 A
4 5 A
6 1 A
SQL> SELECT 5 as start_node, to_node AS end_node, LEVEL as Depth
2 FROM edge
3 WHERE status = 'A'
4 start with to_node=5
5 connect by nocycle prior to_node = from_node and status = 'A' and LEVEL <= 2
6 /
START_NODE END_NODE DEPTH
---------- ---------- ----------
5 5 1
5 5 1
5 5 1
5 5 1
SQL>
SY.
|
|
|
|
|
|
|
|
| Re: Oracles recursive Connect By [message #633084 is a reply to message #633081] |
Tue, 10 February 2015 11:21 |
Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
You provided a query you say produces your posted results. I just proved your query doesn't return result you say it does. So first you need to have a correct query to return your desired results when status = 'A' and, to give you a hint, same query will work when status is I.
SY.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
