Recipe14.10.Converting a Scalar Subquery to a Composite Subquery in Oracle

Recipe 14.10. Converting a Scalar Subquery to a Composite Subquery in Oracle

Problem

You would like to bypass the restriction of returning exactly one value from a scalar subquery. For example, you attempt to execute the following query:

 select e.deptno,        e.ename,        e.sal,        (select d.dname,d.loc,sysdate today           from dept d          where e.deptno=d.deptno)   from emp e 

but receive an error because subqueries in the SELECT list are allowed to return only a single value.

Solution

Admittedly, this problem is quite unrealistic, because a simple join between tables EMP and DEPT would allow you to return as many values you want from DEPT. Nevertheless, the key is to focus on the technique and understand how to apply it to a scenario that you find useful. The key to bypassing the requirement to return a single value when placing a SELECT within SELECT (scalar subquery) is to take advantage of Oracle's object types. You can define an object to have several attributes, and then you can work with it as a single entity or reference each element individually. In effect, you don't really bypass the rule at all. You simply return one value, an object, that in turn contains many attributes.

This solution makes use of the following object type:

 create type generic_obj     as object (     val1 varchar2(10),     val2 varchar2(10),     val3 date  ); 

With this type in place, you can execute the following query:

   1 select x.deptno,  2        x.ename,  3        x.multival.val1 dname,  4        x.multival.val2 loc,  5        x.multival.val3 today  6  from (  7select e.deptno,  8       e.ename,  9       e.sal, 10       (select generic_obj(d.dname,d.loc,sysdate+1) 11          from dept d 12         where e.deptno=d.deptno) multival 13  from emp e 14       ) x DEPTNO ENAME      DNAME      LOC        TODAY ------ ---------- ---------- ---------- -----------     20 SMITH      RESEARCH   DALLAS     12-SEP-2005     30 ALLEN      SALES      CHICAGO    12-SEP-2005     30 WARD       SALES      CHICAGO    12-SEP-2005     20 JONES      RESEARCH   DALLAS     12-SEP-2005     30 MARTIN     SALES      CHICAGO    12-SEP-2005     30 BLAKE      SALES      CHICAGO    12-SEP-2005     10 CLARK      ACCOUNTING NEW YORK   12-SEP-2005     20 SCOTT      RESEARCH   DALLAS     12-SEP-2005     10 KING       ACCOUNTING NEW YORK   12-SEP-2005     30 TURNER     SALES      CHICAGO    12-SEP-2005     20 ADAMS      RESEARCH   DALLAS     12-SEP-2005     30 JAMES      SALES      CHICAGO    12-SEP-2005     20 FORD       RESEARCH   DALLAS     12-SEP-2005     10 MILLER     ACCOUNTING NEW YORK   12-SEP-2005 

Discussion

The key to the solution is to use the object's constructor function (by default the constructor function has the same name as the object). Because the object itself is a single scalar value, it does not violate the scalar subquery rule, as you can see from the following:

  select e.deptno,        e.ename,        e.sal,        (select generic_obj(d.dname,d.loc,sysdate-1)           from dept d        where e.deptno=d.deptno) multival from emp e DEPTNO ENAME  SAL   MULTIVAL(VAL1, VAL2, VAL3) ------ ------ ----- -------------------------------------------------------     20 SMITH   800  GENERIC_OBJ('RESEARCH', 'DALLAS', '12-SEP-2005')     30 ALLEN  1600  GENERIC_OBJ('SALES', 'CHICAGO', '12-SEP-2005')     30 WARD   1250  GENERIC_OBJ('SALES', 'CHICAGO', '12-SEP-2005')     20 JONES  2975  GENERIC_OBJ('RESEARCH', 'DALLAS', '12-SEP-2005')     30 MARTIN 1250  GENERIC_OBJ('SALES', 'CHICAGO', '12-SEP-2005')     30 BLAKE  2850  GENERIC_OBJ('SALES', 'CHICAGO', '12-SEP-2005')     10 CLARK  2450  GENERIC_OBJ('ACCOUNTING', 'NEW YORK', '12-SEP-2005')     20 SCOTT  3000  GENERIC_OBJ('RESEARCH', 'DALLAS', '12-SEP-2005')     10 KING   5000  GENERIC_OBJ('ACCOUNTING', 'NEW YORK', '12-SEP-2005')     30 TURNER 1500  GENERIC_OBJ('SALES', 'CHICAGO', '12-SEP-2005')     20 ADAMS  1100  GENERIC_OBJ('RESEARCH', 'DALLAS', '12-SEP-2005')     30 JAMES   950  GENERIC_OBJ('SALES', 'CHICAGO', '12-SEP-2005')     20 FORD   3000  GENERIC_OBJ('RESEARCH', 'DALLAS', '12-SEP-2005')     10 MILLER 1300  GENERIC_OBJ('ACCOUNTING', 'NEW YORK', '12-SEP-2005') 

The next step is to simply wrap the query in an inline view and extract the attributes.

One important note: In Oracle, unlike the case with other vendors, you do not generally need to name your inline views. In this particular case, however, you do need to name your inline view. Otherwise you will not be able to reference the object's attributes.