Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Help

Re: SQL Help

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 20 Apr 1999 15:33:57 GMT
Message-ID: <37209daa.12523447@192.86.155.100>


A copy of this was sent to "Paul Wiles" <paul_at_adzi.com> (if that email address didn't require changing) On Tue, 20 Apr 1999 16:09:07 +0100, you wrote:

>I'm in the process of rewriting some Transact SQL Sybase code to Oracle
>PL/SQL.
>
>Oracle does not appear to accept embedded subqueries of the form:
>
>select * from TABLE_A
> WHERE
> (VAL1 - (select VAL2 from TABLE_B)
>

select *
  from table_a, ( select val2 from table_b )  where val1 - val2 ....

works just as well if "select val2 from table_b" returns 1 row always.

Another possible way is to hide the query in a function (especially if the query was a correlated query meaning the above would have to have join conditions as well). For example:

select * from table_a
where val1 - some_function( <correlation variables here>) ...

where you have:

create function some_function( <correlation variables here> ) return number as

   l_val2 number;
begin

   select val2 into l_val2 from table_b ....;

   return val2;
end;

>(i know this can be rewitten as select (COL_A,COL_B...) from TABLE_A,TABLE_B
>where VAL1 > VAL2 but the actual queries are more complex and I'd rather not
>rewrite then in this form if I can help it
>
>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 Tue Apr 20 1999 - 10:33:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US