Re: SQL question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 24 Mar 1999 13:05:02 GMT
Message-ID: <36f9e1e6.2928991@192.86.155.100>

A copy of this was sent to Arno van Rossum <a.van.rossum_at_mmp-obec.nl> (if that email address didn't require changing) On Wed, 24 Mar 1999 12:28:17 +0100, you wrote:

>Hello,
>
>Recently I switched from MS SQL-server to Oracle8 but I've some troubles
>converting my stored procedures.
>
>In MS-SQLserver I use by example:
>
> SELECT 1.field1
> , 1.field2
> , (SELECT COUNT(*)
> FROM Table2 2
> WHERE 2.field2 = 1.field1) field3
> FROM Table1 1
> , Table3 3
> WHERE 1.field2 = 3.field1;
>

select a.field1, a.field2, b.field3
  from table1 a,

       (select count(*) field3, field2 from table2 group by field2 ) b,
       table3 c

 where a.field1 = b.field2(+)
   and a.field2 = c.field1
/

would be the equivalent Oracle Syntax. The "(+)" (outer join syntax) is only needed if there might be cases where no rows exist in Table2 for a given a.field1 value....

>But when I tried this in oracle it says I can't use a nested select.
>
>In oracle I tried:
>
> SELECT 1.field1
> , 1.field2
> , field3
> FROM Table1 1
> , Table3 3
> WHERE 1.field2 = 3.field1
> AND field3 = (SELECT COUNT(*)
> FROM Table2 2
> WHERE 2.field2 = 1.field1
> );
>
>but the above statement gives the error that field3 (in the where
>clause) is not known.
>
>Declaring field3 as a local variable in the stored procedure gives no
>errors, but then it selects nothing at all.
>
>Is there someone who can help me translate the above MSSQL-server
>statement to Oracle 8?
>
>Thanks,
>
>Arno van Rossum
>(A.van.Rossum_at_mmp-obec.nl)
 

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 Mar 24 1999 - 07:05:02 CST