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

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

Re: SQL Help

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 20 Apr 1999 15:54:20 GMT
Message-ID: <3723a203.13636528@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:25:22 +0100, you wrote:

>Sorry, I didn't finish off the demo query. Here is the message again:
>
>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)) > 0;
>

actually, there is yet another way (in addition to the other 2 i posted).

SQL> create table table_a as select ename, sal VAL1 from emp;

Table created.

SQL> create table table_b as select 1000 val2 from dual;

Table created.

SQL>
SQL> select *
  2 from table_a
  3 where 0 < ( select table_a.val1 - table_b.val2 from table_b )   4 /

ENAME VAL1
---------- ----------

ALLEN            1600
WARD             1250

....

push the VAL1 into the subquery as I did and put the subquery on the RHS of the operation and it'll go as well.

>(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
>
>
>Paul Wiles wrote in message <371c965d_at_newsread3.dircon.co.uk>...
>>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)
>>
>>(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:54:20 CDT

Original text of this message

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