Why cannot I use subquery there?

Is there any rule where you can use scalar subquery?
You can use a scalar subquery expression in most syntax that calls for an expression (expr).

Well, you cannot use it as the second argument of sys_connect_by_path

select sys_connect_by_path(ename,
   (select '/' from dual)) from emp 
   connect by prior empno=mgr;
*
ERROR at line 1:
ORA-30003: illegal parameter in SYS_CONNECT_BY_PATH 
function

You cannot use it in the ITERATE or in the RULES clause of model

SQL> select * from dual model 
dimension by (0 x) measures (0 y) 
rules iterate ( (select 1 from dual) ) (y[0]=0);
*
ERROR at line 1:
ORA-32607: invalid ITERATE value in MODEL clause
SQL> select * from dual model 
dimension by (0 x) measures (0 y) 
(y[0]=(select 1 from dual));
*
ERROR at line 1:
ORA-32620: illegal subquery within MODEL rules

Also impossible is in the DATAOBJ_TO_PARTITION function that is used in System Partitioning :

SQL> insert into t partition (
  dataobj_to_partition("T",
    (select :partition_id  from dual) )) 
values ('x') ;
*
ERROR at line 1: 
ORA-14198: rowid column must refer to table 
specified in 1st parameter 

Another documented limitation is the GROUP BY clause :


SQL> SELECT (SELECT COUNT(*) FROM EMP), 
  COUNT(*) FROM DEPT ;
*
ERROR at line 1:
ORA-00937: not a single-group group function

SQL> SELECT (SELECT COUNT(*) FROM EMP), COUNT(*) 
  FROM DEPT GROUP BY (SELECT COUNT(*) FROM EMP);
*
ERROR at line 1:
ORA-22818: subquery expressions not allowed here

SQL> SELECT (SELECT COUNT(*) FROM EMP), 
  COUNT(*) FROM DEPT GROUP BY ();

(SELECTCOUNT(*)FROMEMP)   COUNT(*)
----------------------- ----------
                     14          4

8 thoughts on “Why cannot I use subquery there?”

  1. Isn’t this query:

    SQL> SELECT (SELECT COUNT(*) FROM EMP),
    COUNT(*) FROM DEPT ;

    equivalent to this:

    SQL> SELECT (SELECT COUNT(*) FROM EMP),
    COUNT(*) FROM DEPT GROUP BY ();

    ?

    Don’t see the reason to fail one and allow the other.

  2. I would guess that the reason for the apparent difference is that you can’t have an aggregate function/or group function in a non-group query. The function count() requires that only one row is returned, while the sub-query will return an unknown amount of rows.

    This goes for this example also:
    SELECT COUNT(*), TABLE_NAME
    FROM USER_TABLES;

    ERROR at line 1:
    ORA-00937: not a single-group group function

    I would like to state that this is merely a undocumented theory. Anyone have a better explanation?

  3. Oracle fails in noticing the scalar subquery is constant and not correlated.

    I filed a documentation bug since GROUP BY () is not documented in the SQL Reference but it is implictely documented Oracle® Database Data Warehousing Guide, SQL for Aggregation in Data Warehouses
    GROUP BY ROLLUP(a, (b, c))
    This is equivalent to:

    GROUP BY a, b, c UNION ALL
    GROUP BY a UNION ALL
    GROUP BY ()

    note the latest group by !

    in the following query, the group by clause is mandatory, because the subquery is correlated

    select
        (select dname from dept where emp.deptno=dept.deptno),
      count(*)
      from emp
      group by deptno;
    
    (SELECTDNAMEFR   COUNT(*)
    -------------- ----------
    SALES                   6
    RESEARCH                5
    ACCOUNTING              3
    

  4. @Martin Smevik
    count will give output in the form of a single row,then how come we can use that single row value with table_name having multi-row values,it is ambiguos

  5. @Chen Shapira
    Puzzled ==> I am using oracle 10gR2.

    select as_of_date,count(*)
    from(select sysdate as_of_date from dual where 1=0)
    AS_OF_DAT COUNT(*)
    ——— ———-
    26-JUL-11 0
    1 row selected.

    select as_of_date,count(*)
    from(select sysdate as_of_date from dual where 1=0)
    group by as_of_date
    –no rows selected.

    Could you point to documentation on the limitations of group by.

    mail id geo.joseph@gmail.com

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>