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 -> Nested queries (O8)

Nested queries (O8)

From: Eric Dobrzelewski <edobrzel_at_siweb.com>
Date: Mon, 24 May 99 18:29:49 GMT
Message-ID: <7ic5o6$1vq$1@msunews.cl.msu.edu>


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

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   Received on Mon May 24 1999 - 13:29:49 CDT

Original text of this message

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