Printable View
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
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;
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
Yes, you can do that query in 9i (with and without the CURSOR statement)
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
it depends what do you want to do
Code:
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
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.
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.Quote:
Originally posted by stecal
Oracle does not do "Select ColName , (Select ColName From TableName ) from AnotherTableName"
Example:
Code: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>
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.Quote:
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
Example:
Code: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>
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.
Sure it does! Or you should have been clearer again :). Here is an example with no WHERE clause anywhere:Quote:
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.
Code:SQL> select dname, (select dummy from dual) from dept;
DNAME (
-------------- -
ACCOUNTING X
RESEARCH X
SALES X
OPERATIONS X
SQL>
So why didn't the example I posted originally, and the one for 9i, not work? Too many columns in the second select statement? Why does that work in your examples and not mine?
because you have more than one column, if that column was using a defined type it probably works
I have told you allready - because your syntax is wrong! With correct syntax it works both in 8.1.7 and in 9i.Quote:
Originally posted by stecal
So why didn't the example I posted originally, and the one for 9i, not work? Too many columns in the second select statement? Why does that work in your examples and not mine?
Your (wrong) syntax:
SQL> select dname, (select empno, mgr from emp) from dept;
The correct syntax for such example - you must use CURSOR expression:
SQL> select dname, CURSOR(select empno, mgr from emp) from dept;