Nested query in SELECT statement

[Salivador7]

Hello friends,

I have a query that runs perfectly on MS SQL Server, but won't on ORACLE. Please, explain wether such nested queries are allowed in oracle. Example is given below, as you see - the nested query in select statement selects a max value based on field values in select statement. if max is not available, it returns null.

select A.ID, (select max(B.SOMEFIELD) from B where B.ID=A.ID) as test
from A


My query is more complicated, but this is simplified view.

Thank you.

[jmodic]

Which Oracle release?

The above statement will run perfectly fine on 8i (at least it does on 8.1.7) and above. It will probably not run in 8.0 and below, though.

[Salivador7]

Hi.

Oracle 8i.

The error message is:

(select max(B.SOMEFIELD)
*
ERROR at line 4:
ORA-00979: not a GROUP BY expression

I tried to include field name (test) in group by, but I got error message : invalid field name

Any ideas?

[jmodic]

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.1.1 - Production
With the Partitioning option
JServer Release 8.1.7.1.1 - Production

SQL> select
  2    d.dname,
  3    (select max(e.sal) from scott.emp e
  4       where deptno = d.deptno
  5    ) as max_dept_sal
  6  from scott.dept d;

DNAME          MAX_DEPT_SAL
-------------- ------------
ACCOUNTING             5000
RESEARCH               3000
SALES                  2850
OPERATIONS

SQL>

Perhaps you should post your exact query....

[Sweetie]

Hi

that's called scalar subquery and it's new in 8i, perhaps you can research with that name on the net