Printable View
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.
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.
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?
Perhaps you should post your exact query....Code: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>
Hi
that's called scalar subquery and it's new in 8i, perhaps you can research with that name on the net