Re: Subquery Question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 08 Apr 1999 02:34:41 GMT
Message-ID: <370f1517.5790145@192.86.155.100>

A copy of this was sent to Paul Bennett <bennett_at_cc.gatech.edu> (if that email address didn't require changing) On Wed, 07 Apr 1999 16:44:02 -0400, you wrote:

>In some SQL flavors I can do something like
>
>SELECT cola, colb, (Select cola from tablea where tablea.colb =
>tablec.cola) from tablec;
>

we would write that as:

select cola, colb, AnotherCol
from tablec, ( select cola AnotherCol, colb

                 from tablea ) a

where tablec.cola = a.colb (+)
/

or

create function getColA( p_cola in tablec.cola%type ) return tablea.cola%type as

        l_cola tablea.cola%type;
begin

        select cola into l_cola from tablea where colb = p_cola;     return l_cola;
exception

        when no_data_found then return null;
end;
/

select cola, colb, getcola( cola )
from tablec
/

>basically I can use a select that returns one value as a column.
>
>I can not do this in Oracle 8. What is the workaround?
>
>Thanks.
>
>-- Paul
>
 

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 Wed Apr 07 1999 - 21:34:41 CDT