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. Chen Shapira

    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.

  3. Martin Smevik

    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?

  4. Laurent Schneider Post author

    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
  5. karthick

    @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

  7. Pingback: group by () « Sokrates on Oracle

  8. Anonymous

    @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 [email protected]

Comments are closed.