Database Tutorial

Week 10: Reading

Runtime Tables

Learning Outcomes

• Learn how to use queries inside the FROM clause as runtime tables.
• Learn how to use multiple queries inside the FROM clause as runtime tables.

Lesson Materials

A runtime view is a query inside of the FROM clause. It is called a runtime view because it runs at the time the query executes. A runtime view is also called a runtime table and a common table expression (CTE). Microsoft documentation began labelling runtime views as CTEs, and it has become a common way to describe runtime views.

Runtime views are given a name by assigning them a table alias. The runtime view’s name isn’t stored in the data dictionary. The runtime view is only stored in the scope of the query or as an embedded query in an UPDATE statement.

The prototype for a runtime view is:

SELECT  column1 [, column2, ... ]
FROM   (SELECT  column1 [, column2, ... ]
        FROM    table1 table1_alias [JOIN table2 table2_alias
        ON      table1_alias.column1 = table2_alias.column1]
        WHERE   table1_alias.column2 = 'string_literal') rt;

The runtime view runs first and returns a result set. The result set is treated exactly like the contents of an ordinary table. The SELECT-list defines the column names of the runtime table. String, numeric, and date literal values implicitly assign data types to the column names. SELECT-list elements that come from reference to columns in other tables inherit their data types from the columns’ data type in the data dictionary.

Below is a runtime view based on the Video Store model:

SQL> SELECT m.account_number
  2  ,	    rt.first_name || SUBSTR(rt.middle_name,1,1) || rt.last_name AS full_name
  3  ,	    rt.street_address
  4  ,	    rt.city
  5  ,	    rt.state
  6  FROM  (SELECT c.contact_id
  7	    ,	   c.member_id
  8	    ,	   c.first_name
  9	    ,	   c.middle_name
 10	    ,	   c.last_name
 11	    ,	   sa.street_address
 12	    ,	   a.city
 13	    ,	   a.state_province AS state
 14	    ,	   a.postal_code AS zip
 15	    FROM   contact c INNER JOIN address a
 16	    ON	   c.contact_id = a.address_id INNER JOIN street_address sa
 17	    ON	   a.address_id = sa.address_id) rt INNER JOIN member m
 18  ON     m.member_id = rt.member_id;

Lines 6 through 17 contain the runtime view. You should note that it isn’t joined to anything other part of the external query. The parent query only knows the columns that you provide in the runtime view’s SELECT-list.

Let’s say you want to access the runtime view from other queries. Then, you might make the CTE permanent by creating a contact_info view, like:

  1  CREATE OR REPLACE VIEW contact_info AS
  2    SELECT c.contact_id
  3    ,      c.member_id
  4    ,      c.first_name
  5    ,      c.middle_name
  6    ,      c.last_name
  7    ,      sa.street_address
  8    ,      a.city
  9    ,      a.state_province AS state
 10    ,      a.postal_code AS zip
 11    FROM   contact c INNER JOIN address a
 12	      ON     c.contact_id = a.address_id INNER JOIN street_address sa
 13 	      ON     a.address_id = sa.address_id;

Having moved the runtime view into the data dictionary, you can now write the original query with less effort. In the example below, the contact_info view takes the place of the runtime tables. It’s best to create views when runtime tables become routine components of several queries.

SQL> SELECT m.account_number
  2  ,	    ci.first_name || SUBSTR(rt.middle_name,1,1) || rt.last_name AS full_name
  3  ,	    ci.street_address
  4  ,	    ci.city
  5  ,	    ci.state
  6  FROM   contact_info ci INNER JOIN member m
 18  ON     m.member_id = ci.member_id

The results are the same but the statement is much shorter. It is also easier for developers to work with views documented in the data dictionary than runtime tables.