Christian Antognini wrote:
> **** Post for FREE via your newsreader at post.usenet.com ****
>
> Hi
>
>
>>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
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
While this will fix your immediate issue, I would think that you have a
referential integrity problem if you have a "child" table that is missing it's
"parent". Don't you need to fix that problem before you try to solve the
"select" issue...
--
Michael Austin.
Consultant - Available.
Donations welcomed. http://www.firstdbasource.com/donations.html
:)
Received on Wed Jul 21 2004 - 12:33:33 CDT
