Home » SQL & PL/SQL » SQL & PL/SQL » Connect By Query Problem
Connect By Query Problem [message #571495] Mon, 26 November 2012 09:14 Go to next message
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 Go to previous messageGo to next message
_jum
Messages: 486
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 #571500 is a reply to message #571495] Mon, 26 November 2012 09:43 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1949
Registered: January 2010
Senior Member
 select  id2
   from  temptable
   start with id1 = 13
   connect by id1 = prior id2
union
 select  id1
   from  temptable
   start with id2 = 13
   connect by id2 = prior id1
/

       ID2
----------
        11
        12
        14
        15

SQL> 


SY.
Re: Connect By Query Problem [message #571501 is a reply to message #571500] Mon, 26 November 2012 09:47 Go to previous messageGo to next message
_jum
Messages: 486
Registered: February 2008
Senior Member
@SY - I'd say this is not exactly the same, think of:
INSERT INTO temptable
     VALUES (11, 16);
Re: Connect By Query Problem [message #571503 is a reply to message #571499] Mon, 26 November 2012 09:59 Go to previous messageGo to next message
mustafasimsek
Messages: 3
Registered: November 2012
Junior Member
_jum wrote on Mon, 26 November 2012 09:39
The result (ORACLE 11.2.0.3) is as you expected:
BTW: The UNION (without ALL) already gives DISTINCT id1/id2 so DISTINCT is unnecessary.


Thanks _jum. My oracle version is 10.2.0.3.0 and i still get

ID1
----
12
14
15

And as you said, i've tried this query in an 11g version oracle server, and the result was correct. Is this a bug or something?

And is there any other way to rewrite the query to get the correct results in 10.2.0 version?
Re: Connect By Query Problem [message #571504 is a reply to message #571501] Mon, 26 November 2012 10:03 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1949
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.
Re: Connect By Query Problem [message #571505 is a reply to message #571503] Mon, 26 November 2012 10:05 Go to previous messageGo to next message
_jum
Messages: 486
Registered: February 2008
Senior Member
Can only say, that in ORA 10.2.0.5 the result is correct too.
Re: Connect By Query Problem [message #571547 is a reply to message #571504] Tue, 27 November 2012 01:15 Go to previous message
mustafasimsek
Messages: 3
Registered: November 2012
Junior Member

Thanks Solomon, that works Smile



[Edit MC: remove useless quote of whole post]

[Updated on: Tue, 27 November 2012 01:29] by Moderator

Report message to a moderator

Previous Topic: Please can anyone help me sql script equivalent in oracle
Next Topic: If i am not specify data length in the parameters , how much it will take , please can u help me
Goto Forum:
  


Current Time: Wed Jul 23 13:33:02 CDT 2014

Total time taken to generate the page: 0.07702 seconds