Re: Problem converting SELECT with embedded SELECTs

Margaret Ngai wrote:
>
> I am converting a Sybase SELECT query to Oracle. The problem is that the
> original statement contains 'embedded' SELECTs as follows:
>
> SELECT T1.F1, (SELECT T2.F1 WHERE T1.F2 = T2.F2) WHERE T1.F3 = 'xxx'
>
> I need this setup because I need the statement to return T1.F1 even if the
> link to T2 does not exist.
>
> How can I achieve the same result in Oracle? Due to some problem on the
> custom application I am using, I cannot use stored procedures or UNION.
>
> Thanks for any suggestions.
>
> --
> Margaret Ngai
> E-Mail: margaret_at_synrevoice.com

Sounds like an outer join,
try

   SELECT T1.F1,T2.F1
   FROM T1,T2
   WHERE T1.F3='xxx'
   AND T1.F1=T2.F1(+) It'll give you all T1 rows where T1.F3 = 'xxx' regardless of whether or not T2.F1 exists for T1.F1 The oracle manuals describe it ok.
Cheers,
TRG Received on Fri Aug 01 1997 - 00:00:00 CDT