Re: Subqueries in select statements

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 19:03:59 +0200, you wrote:

>I am very frustrated by the fact that oracle 8.0 does not appear to
>accept subqueries as expressions, is this really a fact or is there some
>clever way of doing it?
>
>example :
>select t1.col_a,
> t1.col_b,
> (select max(t2.col_c)
> from table2 t2
> where t1.id=t2.id)
>from table t1

This would be coded as:

select t1.col_a, t1.col_b, t3.col_c
  from table t1, ( select t2.id, max(t2.col_c) col_c

                     from table2 t2
                    group by t2.id ) t3

If all values of t1.id are represented by t2.id, then you could leave the (+) -- outer join syntax -- off.  

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