SQL question

From: Arno van Rossum <a.van.rossum_at_mmp-obec.nl>
Date: Wed, 24 Mar 1999 12:28:17 +0100
Message-ID: <36F8CC51.669167F1@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 Wed Mar 24 1999 - 05:28:17 CST