| Connect By Query Problem [message #571495] |
Mon, 26 November 2012 09:14  |
 |
mustafasimsek
Messages: 3
Registered: November 2012
|
Junior Member |
|
|
Hi everybody,
I've stucked with a query. Let me explain the situation: I have a table that i store the IDs of logically equal records.
For example;
A = B
B = C
X = Y
Z = Y
My query must return all equivalent records. If you call the query with parameter 'A', the result set must contain B and C. And if you call the query with parameter 'Y', the result set will contain X AND Z. I have thought that i can write the query wity using start with connect by statement. But the query does not work as i expected. Here is my code and sample data:
create table temptable (ID1 number,ID2 number);/
insert into temptable values(11,12);/
insert into temptable values(12,13);/
insert into temptable values(13,14);/
insert into temptable values(13,15);/
SELECT distinct ID1 from
(
SELECT * FROM temptable
START WITH ID1 = 13 OR ID2 = 13
CONNECT BY NOCYCLE
(
(PRIOR ID1 = ID1) OR
(PRIOR ID1 = ID2) OR
(PRIOR ID2 = ID1) OR
(PRIOR ID2 = ID2))
) WHERE ID1 <> 13
union
SELECT distinct ID2 from
(
SELECT * FROM temptable
START WITH ID1 = 13 OR ID2 = 13
CONNECT BY NOCYCLE
((PRIOR ID1 = ID1) OR
(PRIOR ID1 = ID2) OR
(PRIOR ID2 = ID1) OR
(PRIOR ID2 = ID2))
) WHERE ID2 <> 13
In my sample the equality definitions is;
11 = 12
12 = 13
13 = 14
13 = 15
When i call the query with parameter 13, i'm expecting to get 11,12,14,15. But it returns only 12,14 and 15.
Thanks for any help or suggestion.
|
|
|
|
| Re: Connect By Query Problem [message #571499 is a reply to message #571495] |
Mon, 26 November 2012 09:39  |
_jum
Messages: 451
Registered: February 2008
|
Senior Member |
|
|
The result (ORACLE 11.2.0.3) is as you expected:
SELECT DISTINCT id1
FROM (
SELECT *
FROM temptable
START WITH id1 = 13 OR id2 = 13
CONNECT BY NOCYCLE (
(PRIOR id1 = id1)
OR (PRIOR id1 = id2)
OR (PRIOR id2 = id1)
OR (PRIOR id2 = id2)))
WHERE id1 <> 13
UNION
SELECT DISTINCT id2
FROM (
SELECT *
FROM temptable
START WITH id1 = 13 OR id2 = 13
CONNECT BY NOCYCLE (
(PRIOR id1 = id1)
OR (PRIOR id1 = id2)
OR (PRIOR id2 = id1)
OR (PRIOR id2 = id2)))
WHERE id2 <> 13;
ID1
-----
11
12
14
15
BTW: The UNION (without ALL) already gives DISTINCT id1/id2 so DISTINCT is unnecessary.
[Updated on: Mon, 26 November 2012 09:44] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Connect By Query Problem [message #571504 is a reply to message #571501] |
Mon, 26 November 2012 10:03 |
Solomon Yakobson
Messages: 1399
Registered: January 2010
|
Senior Member |
|
|
Then:
with t as (
select id1,
id2
from temptable
union
select id2,
id1
from temptable
)
select distinct id2
from t
where id2 != 13
start with id1 = 13
connect by nocycle id1 = prior id2
/
ID2
----------
11
14
12
16
15
SQL>
SY.
|
|
|
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
