Re: Nested queries (O8)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 24 May 1999 19:31:22 GMT
Message-ID: <3750a75b.25847757@newshost.us.oracle.com>

A copy of this was sent to edobrzel_at_siweb.com (Eric Dobrzelewski) (if that email address didn't require changing) On Mon, 24 May 99 18:29:49 GMT, you wrote:

>I'm trying to write what I thought would have been an easy query in Oracle 8.
>It is a nested query that returns different aggregate values as fields. It
>works in Access and SQL Server 6.5 (minor changes). Why doens't this work in
>Oracle 8? What can I do to fake this? I can't do it in a JOIN because the
>inner query WHERE conditions will be different. Here is a simplified example:
>
>SELECT
> (SELECT COUNT(*) FROM tTable2
> WHERE tTable1.fk = tTable2.pk
> ) iField1,
> (SELECT SUM(*) FROM tTable2
> WHERE tTable1.fk = tTable2.pk
> ) iField2
>FROM tTable1
>

that syntax will work in Oracle8i, release 8.1. In 8.0 and before, you would code this as:

select ...
  from ttable1 t1,

      ( select count(*) cnt, pk t2_a_pk
          from tTable2
         group by pk ) t2_a
      ( select sum(<something>), pk t2_b_pk
          from tTable2
         group by pk ) t2_b

 where t1.fk = t2_a_pk(+)
   and t1.fk = t2_b_pk(+)

Your above query would actually (as written) simplify down to:

select ...
  from ttable1 t1,

      ( select count(*) cnt, sum(<something>) s, pk
          from tTable2
         group by pk ) t2_a

 where t1.fk = pk(+)

since the where clauses were the same.

>
>I've tried various incarnations of this. I've put AS after the inner Select.
>I've tried using INTO and putting it into a variable. I've tried doing a
>"varname = (SELECT...)". I just can't figure it out. I really do need to use
>a nested SELECT since the WHERE conditions of each nested SELECT will be
>slightly different. I thought about doing some funky things with GROUP BYs
>and stuff or multiple recordsets, but I'm hoping not to.
>
>The errors I get is, depending how I play with it are:
>Major error code: 936
> [SERVERERROR] Error from Server: ORA-00936: missing expression
>
>-OR-
>Major error code: 923
> [SERVERERROR] Error from Server: ORA-00923: FROM keyword not found where
>expected
>

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon May 24 1999 - 14:31:22 CDT