Re: ** Select (Select ) not possible !!??

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 05 May 1999 19:01:04 GMT
Message-ID: <3738945f.18567198@192.86.155.100>

A copy of this was sent to "Jan Brandsma" <j.brandsma_at_bellt-gca.nl> (if that email address didn't require changing) On Wed, 5 May 1999 18:31:22 +0200, you wrote:

>Hi everyone,
>
>Oracle is new to me, but I was very disappointed that the following query
>doesn't work!
>
>Select Name, (Select Name from Table2 where Table2.Table2ID=Table1.Table2ID)
>from Table1;
>

In Oracle8i, release 8.1, this syntax is supported:

SQL> select empno, ename, ( select dname from dept where deptno = emp.deptno ) 2 from emp;

EMPNO ENAME (SELECTDNAMEFR
---------- ---------- --------------

      7369 SMITH      RESEARCH
      7499 ALLEN      SALES
      7521 WARD       SALES

...

>I am aware of the fact that tis simple query can be solved with joins but
>that means that I have to change code which works fine on Informix, SQL
>Server and Access. Beside that it is also possible to use aggregate
>functions in this manner.
>

It is possible to do any aggregates you do with "select (select)" with "select ... from (select)". For example, you might code:

SQL> select deptno, dname,

  2                  (select count(*) from emp where deptno = dept.deptno) cnt

3 from dept
4 /

    DEPTNO DNAME                 CNT
---------- -------------- ----------
        10 ACCOUNTING              3
        20 RESEARCH                5
        30 SALES                   6
        40 OPERATIONS              0

but thats the same as:

SQL> select dept.deptno, dname, nvl(cnt,0) cnt 2 from dept, ( select count(*) cnt, deptno from emp group by deptno ) emp 3 where dept.deptno = emp.deptno(+) 4 /

    DEPTNO DNAME                 CNT
---------- -------------- ----------
        10 ACCOUNTING              3
        20 RESEARCH                5
        30 SALES                   6
        40 OPERATIONS              0

>Hopefully I missed something and is this possible after all!
>
>Any help is highly appriciated!!
>
>Jan Brandsma
>
>

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed May 05 1999 - 14:01:04 CDT