DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Nested query in SELECT statement

  1. #1
    Join Date
    Aug 2001
    Posts
    12

    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.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  3. #3
    Join Date
    Aug 2001
    Posts
    12
    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?

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  5. #5
    Join Date
    Oct 2000
    Posts
    139
    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
  •  


Click Here to Expand Forum to Full Width