Re: weird type of outer join... matching only 1 row ??

From: Christian Antognini <christian.antognini_at_trivadis.com>
Date: Sat, 17 Jul 2004 00:56:07 +0200
Message-ID: <40f85cdf@post.usenet.com>

Post for FREE via your newsreader at post.usenet.com ****

> I dont even know if this is possible as how can I tell Oracle that if
> there are 2 matches for Key=1 I only wanna match the "first" one... (I
> know there is no such thing as a "first" one...)

Such "rules" can usually be expressed with analytical functions.

In 10g interrow calculation (MODEL clause) is also available.

> If ANYONE can help me it would be much appreciated.

The following SELECT seams to do what you described. Anyway, if you have large tables, it can perform very poorly...

SQL> select * from table1;

KEY VALUE1
---------- ----------

         1       1000
         2       1000

SQL> select * from table2;

KEY F VALUE2
---------- - ----------

         1 A        200
         1 B        300
         1 C         50
         3 A         60

SQL> select table1.key, table2.field,
  2         decode(table2.rn,1,table1.value1,null,table1.value1,null)
value1,
  3         table2.value2
  4  from table1,
  5       (select key, field, value2,
  6               row_number() over (partition by key order by field) rn
  7        from table2) table2

8 where table1.key = table2.key (+)
9 union
10 select table2.key, table2.field, table1.value1, table2.value2 11 from table1, table2
12 where table2.key = table1.key (+)
13 and table1.value1 is null;

KEY F VALUE1 VALUE2
---------- - ---------- ----------

         1 A       1000        200
         1 B                   300
         1 C                    50
         2         1000
         3 A                    60

Chris

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Usenet.com - The #1 Usenet Newsgroup Service on The Planet! *** http://www.usenet.com Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Received on Fri Jul 16 2004 - 17:56:07 CDT