Select Query in Select List

[sunil_virm]

Does Any DBMS supoorts Query like
Select ColName , (Select ColName From TableName ) from AnotherTableName

Where the query in selectList can give the multiple Result .

Infact In Oracle Reference Guide I have found the following Example
SELECT department_name, CURSOR(SELECT salary, commission_pct
FROM employees e
WHERE e.department_id = d.department_id)
FROM departments d;

How Does it supports or give the result

[silasmw]

SELECT department_name, CURSOR(SELECT salary, commission_pct
FROM employees e
WHERE e.department_id = d.department_id)
FROM departments d;

when executed this return an error

ORA-00932: Inconsistent Datatypes,

Cursor is used in declaration section of pl/sql blocks!

Rewrite this query with a normal join:

SELECT d.department_name, e.salary, e.commission_pct
FROM employees e, departments d
WHERE e.department_id = d.department_id;

[stecal]

Oracle does not do "Select ColName , (Select ColName From TableName ) from AnotherTableName"

SQL> select dname, (select job, sal from bonus) from dept;
select dname, (select job, sal from bonus) from dept
*
ERROR at line 1:
ORA-00913: too many values

[rajXesh]

Yes, you can do that query in 9i (with and without the CURSOR statement)

[stecal]

Well, the last example was from 8.1.6. This one is from 9.2.0.1. How does that work now?

oracle9@matjac:{116} sqlplus scott/tiger

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 8 14:04:49 2002

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production


USER NAME SYSDATE
------------------------------ --------- -----------
SCOTT MIGRA40 08-AUG-2002

SQL> select dname, (select empno, mgr from emp) from dept;
select dname, (select empno, mgr from emp) from dept
*
ERROR at line 1:
ORA-00913: too many values

[pando]

it depends what do you want to do

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 8 22:21:30 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

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

ENAME      DNAME
---------- --------------
CLARK      ACCOUNTING
KING       ACCOUNTING
MILLER     ACCOUNTING



SQL*Plus: Release 8.1.7.0.0 - Production on Thu Aug 8 22:21:15 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

lsc@DEV817>select ename, (select dname from dept where deptno = 10) dname
  2  from emp
  3  where deptno = 10;

ENAME      DNAME
---------- --------------
CLARK      ACCOUNTING
KING       ACCOUNTING
MILLER     ACCOUNTING

[Shestakov]

If u have to use internal select in a select list, that this (internal) select
MUST return ONE an ONLY ONE row for each rows of "extenal" select.
pando showed this situation in his examples.

[jmodic]

Originally posted by stecal
Oracle does not do "Select ColName , (Select ColName From TableName ) from AnotherTableName"

Oh yes, it does. At least from 8.1.7 and above, not sure about 8.1.6. The only catch is that a subquery in a select list must return only one value - that is select mus result in a single row-single coulumn resultset.

Example:

SQL> select d.dname,
  2        (select sum(sal) from emp
  3         where deptno=d.deptno) total_sal
  4  from dept d;

DNAME           TOTAL_SAL
-------------- ----------
ACCOUNTING           8750
RESEARCH            10875
SALES                9400
OPERATIONS

SQL>

Well, the last example was from 8.1.6. This one is from 9.2.0.1. How does that work now?

SQL> select dname, (select empno, mgr from emp) from dept;
select dname, (select empno, mgr from emp) from dept
*
ERROR at line 1:
ORA-00913: too many values

It works if you use the correct syntax. When a subselect in a select list returns more than one column or more than one row (or both), you must use so called "cursor expression", which is basicaly the same as REFCURSOR in PL/SQL.

Example:

SQL> SELECT dname,
  2    CURSOR(SELECT sal, mgr 
  3           FROM emp e
  4           WHERE e.deptno = d.deptno)
  5  FROM dept d
  6  WHERE deptno=10;

DNAME          CURSOR(SELECTSAL,MGR
-------------- --------------------
ACCOUNTING     CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

       SAL        MGR
---------- ----------
      2450       7839
      5000
      1300       7782

SQL>

[stecal]

Thanks, I know about inline selects. I should have been clearer - the way the question was posed, without any where clause that restricts the output, would not and does not work.

[jmodic]

Originally posted by stecal
I should have been clearer - the way the question was posed, without any where clause that restricts the output, would not and does not work.

Sure it does! Or you should have been clearer again . Here is an example with no WHERE clause anywhere:

SQL> select dname, (select dummy from dual) from dept;

DNAME          (
-------------- -
ACCOUNTING     X
RESEARCH       X
SALES          X
OPERATIONS     X

SQL>