topic Re: Corelated Subquery ? in Operating System - HP-UX

Corelated Subquery ?

uform — Tue, 20 Feb 2007 11:27:49 GMT

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

Re: Corelated Subquery ?

Hein van den Heuvel — Tue, 20 Feb 2007 12:35:14 GMT

Hpux?

Oracle?

Flat-files?

Hein.

Re: Corelated Subquery ?

uform — Tue, 20 Feb 2007 12:51:46 GMT

Sorry.. ORACLE

Re: Corelated Subquery ?

Sandman! — Tue, 20 Feb 2007 13:37:30 GMT

Instead of using a correlated subquery try using inline tables.

~cheers

Re: Corelated Subquery ?

uform — Tue, 20 Feb 2007 13:42:15 GMT

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

Thanks

Re: Corelated Subquery ?

Sandman! — Tue, 20 Feb 2007 13:59:19 GMT

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

Re: Corelated Subquery ?

Hein van den Heuvel — Tue, 20 Feb 2007 23:09:11 GMT

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.