Re: Select in Select

Bessenyei Zsolt <bessenyei_at_flexum.hu> wrote in message news:380AFD1B.CEB99D41_at_flexum.hu...
> Hello,
> When I run this query:
> select field1, (select field2 from table2 where field3=1) from table1
> this error message appears:
> ORA-00936: missing expression
> Exactly the same query runs well on Sybase SQL Anywhere and MSSQL.
> How can I use a SELECT as a field ?
> Zsolt Bessenyei

This feature is ONLY available in Oracle8i, and it's undocumented. So many persons don't believe this statement can work in Oracle. You can do this in Oracle8i:

SQL> select ename, (select dname from dept where deptno=20) from emp;

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

SMITH      RESEARCH
ALLEN      RESEARCH
WARD       RESEARCH
JONES      RESEARCH
MARTIN     RESEARCH
BLAKE      RESEARCH
CLARK      RESEARCH
SCOTT      RESEARCH
KING       RESEARCH
TURNER     RESEARCH
ADAMS      RESEARCH
JAMES      RESEARCH
FORD       RESEARCH
MILLER     RESEARCH

14 rows selected.

In Oracle8.0 and prior, you must use an alternative way:

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

ENAME DNAME
---------- --------------

SMITH      RESEARCH
ALLEN      RESEARCH
WARD       RESEARCH
JONES      RESEARCH
MARTIN     RESEARCH
BLAKE      RESEARCH
CLARK      RESEARCH
SCOTT      RESEARCH
KING       RESEARCH
TURNER     RESEARCH
ADAMS      RESEARCH
JAMES      RESEARCH
FORD       RESEARCH
MILLER     RESEARCH

14 rows selected.

The subquery can only return one record in the above examples. If the subquery contains multiple records, you must use the folloing way (only for Oracle8i):

SQL> create or replace type number_nt as table of number;   2 /

Type created.

SQL> select dname, cast(multiset(select empno from emp where deptno=d.deptno) as number_nt)
  2 from dept d;

DNAME
CAST(MULTISET(SELECTEMPNOFROMEMPWHEREDEPTNO=D.DEPTNO)ASNUMBER_NT)

-------------- -------------------------------------------------------------
----
ACCOUNTING     NUMBER_NT(7782, 7839, 7934)
RESEARCH       NUMBER_NT(7369, 7566, 7788, 7876, 7902)
SALES          NUMBER_NT(7499, 7521, 7654, 7698, 7844, 7900)
OPERATIONS     NUMBER_NT()