-
Nested query in SELECT statement
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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?
-
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>
Perhaps you should post your exact query....
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Hi
that's called scalar subquery and it's new in 8i, perhaps you can research with that name on the net
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|