Adsense Ad

Wednesday 12 April 2017

Oracle SQL: Inline View

Overview


The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.
OK, so why use the complicated select in the first place?  Why not just create the view? Well, one good reason is that creating a view gives you another database object to maintain, and adds more complexity to your system.  By placing the view "inside" your main select, you have all of the code needed to support the query in one place.
If you have a query as the following ...
SELECT a
  FROM table
 WHERE id = :id
   AND b = (SELECT MAX (b)
                       FROM table
                      WHERE id = :id)
... it can be worth to check if an inline view, instead of the sub-query will be faster.

Example (cannot have join with CONNECT BY)

Have you ever tried to join to a hierarchical query (a query using CONNECT BY and PRIOR) only to get this message:
ORA-01437: cannot have join with CONNECT BY
One of the limitations of hierarchical queries is that you cannot join to them. However, there are often times you would like to join to them anyway. For instance, if the hierarchy table only has surrogate keys, and you would like to display the real value. This tip shows how you can use "Inline Views" to join tables to a hierarchical query.
SELECT level, LPAD(' ',2*level-2)||ename ename, empno, mgr, dept.deptno, dept.dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
CONNECT BY PRIOr empno = mgr
START WITH empno = 7839;
ORA-01437: cannot have join with CONNECT BY
SELECT E.emplevel, SUBSTR(E.ename,1,15) "ENAME", E.empno, dept.deptno, dept.dname
 FROM dept, (SELECT level emplevel, LPAD('   ',2*level-2)||ename ename, empno, mgr, deptno
               FROM emp
               CONNECT BY PRIOR empno = mgr
               START WITH empno = 7839) E

WHERE E.deptno = dept.deptno
/

  EMPLEVEL ENAME                EMPNO     DEPTNO DNAME
---------- --------------- ---------- ---------- --------------
         1 KING                  7839         10 ACCOUNTING
         2   CLARK               7782         10 ACCOUNTING
         3     MILLER            7934         10 ACCOUNTING
         2   JONES               7566         20 RESEARCH
         3     SCOTT             7788         20 RESEARCH
         4       ADAMS           7876         20 RESEARCH
         3     FORD              7902         20 RESEARCH
         4       SMITH           7369         20 RESEARCH
         2   BLAKE               7698         30 SALES
         3     ALLEN             7499         30 SALES
         3     WARD              7521         30 SALES
         3     MARTIN            7654         30 SALES
         3     TURNER            7844         30 SALES
         3     JAMES             7900         30 SALES

Example (ROWNUM 1 Problem)

A rownum restriction starting with 1 works:
ROWNUM does not work for ranges that don't start at 1.
A ROWNUM restriction starting with 1 works:
SELECT ROWNUM,ename from emp WHERE ROWNUM BETWEEN 1 and 3
/

    ROWNUM ENAME
---------- ----------
         1 SMITH
         2 ALLEN
         3 WARD
However, if you try to use a range it will not work. For example:
SELECT ROWNUM,ename from emp WHERE ROWNUM BETWEEN 2 and 3
/

no rows selected
Using an Inline View to get around this limitation:
SELECT t1.rn, t1.ename
  FROM (SELECT ROWNUM rn, ename
          FROM emp
) t1
 WHERE t1.rn BETWEEN 2 and 3
/
The main trick to this query is the "internal" select statement. This select statement in the from clause, basically does a full query of the table, then returns the values (along with the psuedo-column ROWNUM) to the "outside" query.  The outside query can then operate on the results of the internal query.  In order to access the internal query's columns from the external query, you need to give the internal query an alias ("t1" highlighted below): This allows you to refer to the columns using the "t1" (highlighted below): Since "ROWNUM" is a psuedo-column and therefore a reserved word, you need to alias that column in the internal query in order to refer to it in the outside query:

Example (ROWNUM and ORDER BY Problem, TOP-N Queries)

The following query form is almost wrong:
select * from emp where ROWNUM <= 5 order by sal desc;  /* WRONG! */

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
The users intention was most likely to get the the top-five paid people - a top-N query.  What the will get is five random records (the first five we happen to hit), sorted by salary. If you use an inline view with the ORDER BY inside the inline view, you get the correct result.
select * from (select * from emp order by sal desc) where rownum <= 5;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

Example (Pagination with ROWNUM)

Pagination with ROWNUM can be used to get rows N thru M of a result set. The general form of this is as follows:
SELECT *
  FROM (SELECT a.*, ROWNUM rn
          FROM (enter your query here) a
         WHERE ROWNUM <= :MAX_ROW)
 WHERE rn >= :MIN_ROW;
SELECT *
  FROM (SELECT a.*, ROWNUM rn
          FROM (SELECT * FROM emp) a
         WHERE ROWNUM <= 6)
 WHERE rn >= 2;


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30          2
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30          3
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20          4
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30          5
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30          6

Example (Simplify complex Inline View with an Object Type)

Sometimes, complex Inline Views can be simplified by an Object Type.
SELECT dname,
       LTRIM(SUBSTR(IV, 1, 30 )) ename,
       LTRIM(SUBSTR(IV, 31, 10 )) job,
       TO_DATE(SUBSTR(IV, 40),'yyyymmddhh24miss') hiredate
  FROM (
         SELECT dname,
              (
                SELECT RPAD(ename,30)||RPAD(job,30)||TO_CHAR(hiredate,'yyyymmddhh24miss')
                  FROM emp
                 WHERE rownum = 1
                   AND emp.deptno = dept.deptno

              ) IV
           FROM dept
       )
/

DNAME          ENAME                          JOB        HIREDATE
-------------- ------------------------------ ---------- ---------
ACCOUNTING     CLARK                          MANAGER    09-JUN-81
RESEARCH       SMITH                          CLERK      17-DEC-80
SALES          ALLEN                          SALESMAN   20-FEB-81
OPERATIONS
With an Object Type
CREATE OR REPLACE TYPE FormatType AS OBJECT
(
   ename     VARCHAR2(30),
   job       VARCHAR2(10),
   hiredate  DATE)
/

SELECT dname,
       IV1.IV2.ename ename,
       IV1.IV2.job job,
       IV1.IV2.hiredate hiredate
  FROM (
          SELECT dname,
               (

                 SELECT FormatType(ename,job,hiredate)
                   FROM emp
                  WHERE rownum = 1
                    AND emp.deptno = dept.deptno
               ) IV2

            FROM dept
       ) IV1

/

DNAME          ENAME                          JOB        HIREDATE
-------------- ------------------------------ ---------- ---------
ACCOUNTING     CLARK                          MANAGER    09-JUN-81
RESEARCH       SMITH                          CLERK      17-DEC-80
SALES          ALLEN                          SALESMAN   20-FEB-81
OPERATIONS
The select statement within the select statement must always return single row, otherwise we will get the following error
ORA-01427: single-row subquery returns more than one row. 

No comments: