INLINE view in Oracle with 12c New Features

• Named sub Query in FROM clause is call it as INLINE VIEW.
• Oracle process a inline view faster than sub query

Inline View in Oracle

--Query to display all the employees who are all getting salary more than their department average salary

SELECT First_name

     , last_name

     , salary

     , department_id

  FROM employees a , ( SELECT b.department_id 

      , AVG(b.salary) avg_sal

           FROM employees b

                              GROUP BY b.department_id

) b

 WHERE a.department_id = b.department_id

   AND a.salary > b.avg_salary;

Display all the customer who have placed more number of orders by using INLINE view

CREATE TABLE customer_order_det_tb

  ( custm_id NUMBER               ,

    custm_nm VARCHAR2(30)         ,

    order_dt DATE DEFAULT SYSDATE ,

    ttl_amt  NUMBER(7,2)  

  );

BEGIN  

  INSERT INTO customer_order_det_tb VALUES(100, 'Murugappan', '11-Jul-15' , 45000.00);

  INSERT INTO customer_order_det_tb VALUES(100, 'Murugappan', '14-Jul-15' , 27000.00);

  INSERT INTO customer_order_det_tb VALUES(101, 'Raja'      , '13-Jul-15' , 17000.00);

  INSERT INTO customer_order_det_tb VALUES(102, 'Ramesh'    , '15-Jul-15' , 34000.00);

  INSERT INTO customer_order_det_tb VALUES(102, 'Ramesh'    , '15-Jul-15' , 38000.00);

  COMMIT;

END;

SELECT c.* 

  FROM customer_order_det_tb c;

--------------------------------------------

custm_id  custm_nm      order_dt    ttl_amt

--------------------------------------------

100       Murugappan 7/11/2015   45000.00

100       Murugappan 7/14/2015   27000.00

101       Raja          7/13/2015   17000.00

102       Ramesh 7/15/2015   34000.00

102       Ramesh 7/15/2015   38000.00

--------------------------------------------

--Writing a Quer to display all the customer who have placed maximum number of order

--Query #1 for inline view

                

  SELECT c.custm_id, c.custm_nm, COUNT(*) total_odr

    FROM customer_order_det_tb c 

GROUP BY c.custm_id,c.custm_nm

ORDER BY total_odr DESC;

--------------------------------

custm_id  custm_nm    total_odr

--------------------------------

     100  Murugappan 2

     102  Ramesh 2  

     101  Raja        1

--------------------------------

--Query #2 for inline view

  SELECT MAX(COUNT(*)) total_odr

    FROM customer_order_det_tb cnt

GROUP BY cnt.custm_id, cnt.custm_nm;

----------

total_odr

----------

     2

----------

Inline view to find all the customers who have placed maximum number of orders

/**************************************** 

Syntax 

SELECT * FROM <query1> x, <query2> y

WHERE contition; 

****************************************/

SELECT query1.* FROM 

         (   SELECT c.custm_id, c.custm_nm, COUNT(*) total_odr

               FROM customer_order_det_tb c 

           GROUP BY c.custm_id,c.custm_nm

           ORDER BY total_odr DESC

         ) query1,

         (   SELECT MAX(COUNT(*)) total_odr

               FROM customer_order_det_tb cnt

           GROUP BY cnt.custm_id, cnt.custm_nm  

         ) query2

WHERE query1.total_odr = query2.total_odr;

-------------------------------

custm_id  custm_nm    total_odr

-------------------------------   

     100  Murugappan 2 

     102  Ramesh 2

-------------------------------

DROP TABLE customer_order_det_tb;

--Display name and salary for top three money makers of the company and their designation should not be SALESMAN and PRESIDENT

SELECT first_name, salary

  FROM (  SELECT first_name, salary

            FROM employees e

           WHERE job_id NOT IN ('SALESMAN','PRESIDENT')

        ORDER BY salary DESC

)

 WHERE ROWNUM < 4;

New Oracle 12c Inline view Syntax

• LATERAL clause for In-line views
• Allows for columns in the inline view to be accessed!!

Example :

--in 11g

SELECT * FROM employees e, (

SELECT * 

 FROM departments d

        WHERE e.department_id = d.department_id);

ORA-0090: "E"."DEPARTMENT_ID": Invalid Identifier

--in 12c

SELECT * FROM employees e, LATERAL(

                                    SELECT * 

                                      FROM departments d

            WHERE e.department_id = d.department_id

                                  );

--in 11g

WITH t 

AS              

(   SELECT LEVEL a 

      FROM dual 

CONNECT BY LEVEL <= 30)

        SELECT * 

          FROM t, (SELECT * 

                     FROM employees

                    WHERE department_id = t.a

                  );

                  

ORA-00904: "T"."A": invalid Identifier

--in 12c

WITH t 

AS              

(SELECT LEVEL a FROM dual CONNECT BY LEVEL <= 30)

        SELECT * FROM t,

        LATERAL(SELECT  * 

           FROM employees

          WHERE department_id = t.a); 

Hope you have enjoyed this little article 

                Feed backs are always Welcome :) (: