Re: Subqueries in select statements

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 27 Apr 1999 19:48:00 GMT
Message-ID: <37291467.32124512_at_192.86.155.100>

A copy of this was sent to Øystein Johnsen <oystein_at_premium.no> (if that email address didn't require changing) On Tue, 27 Apr 1999 21:30:45 +0200, you wrote:

>Thanks a lot for your answer, I am new to Oracle's syntax, being a long
>term Microsoft user, but I am still a little lost.
>
>To complicate matters, what I really need is this:
>
>>select t1.col_a,
>> t1.col_b,
>> exp(isnull((select sum(log(t2.col_c)))
>> from table2 t2
>> where t1.id=t2.id and t2.date_column<t1.date_column)
>>from table t1
>
>I have found out the the equivalent for "isnull" is "nvl" and "log" is
>"ln", but the
>datepart complicates things a bit.
>
>What I am really doing here is that for each row in the outer table, I
>want the product of some factors in another table up to a date specified
>for each row.
>
>Any help would be greatly appreciated.

I ran a small test like:

drop table t1;
drop table t2;

create table t1 ( id int, col_a int, col_b int, date_column date );

create table t2 ( id int, col_c int, date_column date );

insert into t1 values ( 1, 1, 1, sysdate );
insert into t1 values ( 2, 1, 1, sysdate );
insert into t1 values ( 3, 1, 1, sysdate );

insert into t2 values ( 2, 4, sysdate-2 );

insert into t2 values ( 2, 4, sysdate-1 ); insert into t2 values ( 2, 4, sysdate+1 );

insert into t2 values ( 3, 4, sysdate+2 );
insert into t2 values ( 3, 4, sysdate+1 );
insert into t2 values ( 3, 4, sysdate+1 );

select t1.id, t1.col_a, t1.col_b, exp(nvl(sum(ln(t2.col_c)),0)), count(t2.id)   from t1, t2
 where t1.id = t2.id (+)
   and t2.date_column (+) < t1.date_column  group by t1.id, t1.col_a, t1.col_b
/

And the output is:

        ID COL_A COL_B EXP(NVL(SUM(LN(T2.COL_C)),0)) COUNT(T2.ID)

---------- ---------- ---------- ----------------------------- ------------
         1          1          1                             1            0
         2          1          1                            16            2
         3          1          1                             1            0

It shows that 0 records from T2 contributed to the result set for ID=1 (as expected, there are none) and for ID=3 (as expected, all of the dates in T2 are bigger then the dates in T1 for ID=3). For ID=2, it contributed 2 records -- as expected since the dates were input that way.

I believe that query is what you are after. t1.id does not need to be SELECTED but it does need to be part of the GROUP BY (to preserve the original rows in T1). count(t2.id) does not need to be SELECTED, i only selected it to see that the right number of rows were being joined.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Received on Tue Apr 27 1999 - 21:48:00 CEST