Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

Corelated Subquery ?

uform
Frequent Advisor

Corelated Subquery ?

Hi,

I need to find particular column value(ColActual) and
get its replacement item (colReplace) AND
if ColReplace is available as ColActual
then i need to find the colReplace for that record and display it.

For Example

Sample data

ColActual ----- ColReplace
Item1---------- Item4
Item4---------- Item10


My output should be "Item 10" based on above sample data , because
1) First i will find Item 1 and find its replace which is Item4.
2) Then i'll check for Item4 in "ColActual" which is present , so then i will have to
get its ColReplace value which Item 10.


Problem.....
i have situations like following...check Item1 referring back to itself.

ColActual ----- ColReplace
Item2---------- Item6
Item3---------- Item10
Item1---------- Item4
Item6---------- Item9
Item11---------- Item14
Item4---------- Item1

My requirement now is to identify ONLY such recurrsive data.I need
both records in my output (if possible in ordered fashion).
For example , for the given data the output should be ,

ColActual ----- ColReplace
Item1---------- Item4
Item4---------- Item1

Is this possible in one query. I cant write a procedure for this as i dont have access to create procedures.

PLS HELP .. Thanks
6 REPLIES
Hein van den Heuvel
Honored Contributor

Re: Corelated Subquery ?

Hpux?

Oracle?

Flat-files?

Hein.
uform
Frequent Advisor

Re: Corelated Subquery ?

Sorry.. ORACLE
Sandman!
Honored Contributor

Re: Corelated Subquery ?

Instead of using a correlated subquery try using inline tables.

~cheers
uform
Frequent Advisor

Re: Corelated Subquery ?

Can you please send me a sample of inline table usage. i'm new to Oracle.

Thanks
Sandman!
Honored Contributor

Re: Corelated Subquery ?

Posted below is a sample of inline table usage that you can tailor to your issue:

select * from your_table yt,
(select ColActual from your_table) t1
where t1.ColActual = yt.ColReplace
/

~hope it helps
Hein van den Heuvel
Honored Contributor

Re: Corelated Subquery ?

Again (3rd time today!) a solution is suggested in the "SQL COOKBOOK"
Chapter 11, "Advanced Searching: Determining Which Rows Are Reciprocals"
http://www.oreilly.com/catalog/sqlckbk/

SQL> drop table test;
Table dropped.
SQL> create table test (ColActual varchar(10), ColReplace varchar(10));
Table created.
SQL> set feedback off
SQL> insert into test values ( 'Item2', 'Item6');
SQL> insert into test values ( 'Item3', 'Item10');
SQL> insert into test values ( 'Item1', 'Item4');
SQL> insert into test values ( 'Item6', 'Item9');
SQL> insert into test values ( 'Item11', 'Item14');
SQL> insert into test values ( 'Item4', 'Item1');
SQL> select * from test;

COLACTUAL COLREPLACE
---------- ----------
Item2 Item6
Item3 Item10
Item1 Item4
Item6 Item9
Item11 Item14
Item4 Item1
SQL> select distinct v1.* from test v1, test v2
2 where v1.ColActual = v2.ColReplace
3 and v2.ColActual = v1.ColReplace
4
SQL> /

COLACTUAL COLREPLACE
---------- ----------
Item4 Item1
Item1 Item4
SQL>

note, this does NOT catch loops with more than 1 hop!

Cheers,
Hein.