-
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.
-
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:
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>
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:
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>
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
-
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:
Code:
SQL> select dname, (select dummy from dual) from dept;
DNAME (
-------------- -
ACCOUNTING X
RESEARCH X
SALES X
OPERATIONS X
SQL>
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|