Re: Subquery in Select

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 03 Mar 1999 15:41:44 GMT
Message-ID: <36dd55c9.5810765@192.86.155.100>

A copy of this was sent to "Stefan Sandow" <stefan.sandow_at_gmx.de> (if that email address didn't require changing) On Wed, 3 Mar 1999 15:50:57 +0100, you wrote:

>Please read the two questions and answers that I have copied from the
>newsgroups archive, and give me a right answer. I know there is always a
>*workaround*, but I want to do it straight. So: DOES Oracle support the
>questioned syntax or not? I've searched the doc's and the newsgroups, but
>didn't find any peace of code that answered the question.
>
>Thanks in advance!
>Stefan
>

we do not support subqueries in selects exactly in the way described below.

The most common use of this is to do a lookup, for example:

select dept.dname, ( select count(*) from emp where emp.deptno = dept.deptno )   from dept
/

that gets a list of dept's and a count of employees. In oracle you could code that in a couple of different ways, one of which is:

select dept.dname, cnt
  from dept, ( select deptno, count(*) cnt from emp group by deptno ) emp  where dept.deptno = emp.deptno
/

You would put the NON-CORRELATED subquery into the from clause and join to it with the correlation variables you used in the correlated subquery above (eg: in this example the correlation variables are deptno)

We can use selects in a 'select' list for object queries:

SQL> create or replace type empType as object

  2  (   empno       number,
  3      ename       varchar2(10),
  4      job         varchar2(9),
  5      hiredate    date,
  6      sal         number,
  7      comm        number

  8 );
  9 /

Type created.

SQL>
SQL> create or replace type empArrayType as table of empType;   2 /

Type created.

SQL>
SQL> select d.deptno, d.dname, d.loc,

  2         CAST( multiset( select empno, ename, job, hiredate, sal, comm
  3                           from emp
  4                         where emp.deptno = d.deptno ) AS empArrayType ) EMPS

  5 from dept d
  6 /

    DEPTNO DNAME LOC EMPS(EMPNO, ENAME, JOB, HIREDA

---------- -------------- ------------- ------------------------------
        10 ACCOUNTING     NEW YORK      EMPARRAYTYPE(EMPTYPE(7782, 'CL
                                        ARK', 'MANAGER', '09-JUN-81',
                                        2450, NULL), EMPTYPE(7839, 'KI
                                        NG', 'PRESIDENT', '17-NOV-81',
                                         5000, NULL), EMPTYPE(7934, 'M
                                        ILLER', 'CLERK', '23-JAN-82',
                                        1300, NULL))

        20 RESEARCH       DALLAS        EMPARRAYTYPE(EMPTYPE(7369, 'SM
                                        ITH', 'CLERK', '17-DEC-80', 80
                                        0, NULL), EMPTYPE(7566, 'JONES
                                        ', 'MANAGER', '02-APR-81', 297
                                        5, NULL), EMPTYPE(7788, 'SCOTT
                                        ', 'ANALYST', '09-DEC-82', 300
                                        0, NULL), EMPTYPE(7876, 'ADAMS
                                        ', 'CLERK', '12-JAN-83', 1100,
                                         NULL), EMPTYPE(7902, 'FORD',
                                        'ANALYST', '03-DEC-81', 3000,
                                        NULL))

        30 SALES          CHICAGO       EMPARRAYTYPE(EMPTYPE(7499, 'AL
                                        LEN', 'SALESMAN', '20-FEB-81',
                                         1600, 800), EMPTYPE(7521, 'WA
                                        RD', 'SALESMAN', '22-FEB-81',
                                        1250, 500), EMPTYPE(7654, 'MAR
                                        TIN', 'SALESMAN', '28-SEP-81',
                                         1250, 1400), EMPTYPE(7698, 'B
                                        LAKE', 'MANAGER', '01-MAY-81',
                                         2850, NULL), EMPTYPE(7844, 'T
                                        URNER', 'SALESMAN', '08-SEP-81
                                        ', 1500, 0), EMPTYPE(7900, 'JA
                                        MES', 'CLERK', '03-DEC-81', 95
                                        0, NULL))

        40 OPERATIONS     BOSTON        EMPARRAYTYPE()




>**


>**Hello,

>**
>**I'm new to Oracle DB's (and this newsgroup), so forgive me if this
>question
>**was already asked and answered a hundred times...
>**
>**To my knowledge, Oracle doesn't support subqueries in the field selection
>**list. Is this still true with current releases? If so, what work-arounds
>do
>**you use to achieve similar results? If not, what release of Oracle
>supports
>**subqueries within the SELECT statement?
>**
>**Thanks for any info you can provide.
>**
>**
>**Allen Worthington
>>Subqueries within field selection lists, or in-line views as ORACLE terms
>them,
>>are available with ORACLE. I'm not sure whether it was in 7.2 or 7.3 of
>ORACLE
>>that they arrived, but I think it was 7.2
>>
>>HTH
>> Rob
>
>**I am trying to retrieve a description value from a table while querying
>**another but I can't get the SQL text
>**right. For example, I need the order_id and the employee name instead of
>its
>**id.**SELECT order_id,
>**(SELECT emp_name FROM Employees WHERE Employees.emp_id = Orders.emp_id) AS
>**"Name"
>**FROM Orders
>**WHERE order_Item = 'soap'
>**
>**The SQL works fine in SQL Server or Access, with minor modifications.
>>this would simply be:select order_id, emp_name "Name"from orders, employees
>>where employees.emp_id = orders.emp_idand order_item = 'soap'
>>
>
>
 

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

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Mar 03 1999 - 09:41:44 CST