Laurent Schneider

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

This entry was posted on Thursday, January 24th, 2008 at 12:30 and is filed under oracle, sql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Post to : del.icio.us - Digg it - Furl - reddit - Spurl

Put your code in <code> and </code> tags

4 Responses to “Why cannot I use subquery there?”

Chen Shapira Says:
January 25th, 2008 at 06:13
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.
Laurent Schneider Says:
January 25th, 2008 at 07:53
me neither
Martin Smevik Says:
January 28th, 2008 at 15:27
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?

Laurent Schneider Says:
January 28th, 2008 at 16:50

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

Use <code> and </code> to post code

Why cannot I use subquery there?

4 Responses to “Why cannot I use subquery there?”

Leave a Reply