Select Query in Select List
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Select Query in Select List

  1. #1
    Join Date
    Aug 2002
    Posts
    3
    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

  2. #2
    Join Date
    Jul 2002
    Posts
    9

    Red face

    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;

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    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

  4. #4
    Join Date
    Aug 2002
    Posts
    1
    Yes, you can do that query in 9i (with and without the CURSOR statement)

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    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

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  7. #7
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  9. #9
    Join Date
    May 2002
    Posts
    2,645
    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.

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
  •  


Click Here to Expand Forum to Full Width