Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Nested queries (O8)
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
![]() |
![]() |