Re: SQL question

select
  1.field1,
   dummy.cnt
 from
  table1 1,
  table2 2,
  (select count(*) cnt, 2.field2 field2 from table2 2) dummy where
  1.field2=3.field1
  and
  1.field1=dummy.field2

You cannot use a nested query in the select list. You cannot reference values from other tables in the from clause. Must do this in the where clause.

Greetings, JM

Arno van Rossum wrote in message <36F8CC51.669167F1_at_mmp-obec.nl>...
>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;
>
>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)
Received on Thu Mar 25 1999 - 06:10:06 CST