IBM PureData System for Analytics, Version 7.1

WITH clause

Use the WITH clause to improve query speed for complex subqueries, without the need for conversion. This is also called subquery factoring, and is used when a subquery is started multiple times.

The WITH clause syntax allows it to be used wherever the SELECT syntax was acceptable in the past (INSERT, UPDATE, DELETE, CTAS, and SELECT).

Recursive queries for the WITH Clause are not supported.

Before downgrading to an IBM® Netezza® system version that does not support the With Clause syntax, all SQL objects (views and stored procedures) that use this new syntax must be removed from the system.

Syntax

Syntax for using WITH clause:

<query expression > ::=
    [ <WITH clause>] <query expression body>;
<with list> ::=
      <with list element> [ { <comma> <with list element> }… ]

<with list element> ::=
      <query name> [ <left paren> <with column list> <right paren> ]
         AS <left paren> <query expression> <right paren> [ <search or 
cycle clause> ]

<with column list> ::= <column name list>

<query expression body> ::=
      <query term>
      | <query expression body> UNION [ ALL | DISTINCT ]
         [ <corresponding spec> ] <query term>
      | <query expression body> EXCEPT [ ALL | DISTINCT ]
         [ <corresponding spec> ] <query term>

<query term> ::=
      <query primary>
      | <query term> INTERSECT [ ALL | DISTINCT ]
         [ <corresponding spec> ] <query primary>

<query primary> ::=
      <simple table>
      | <left paren> <query expression body> <right paren>

<simple table> ::=
<query specification>
| <table value constructor>
| <explicit table>

<explicit table> ::= TABLE <table or query name>

<corresponding spec> ::=
      CORRESPONDING [ BY <left paren> <corresponding column list> <right 
paren> ]

<corresponding column list> ::= <column name list>

Inputs

The WITH clause takes the following inputs:

Table 1. WITH clause inputs
Input	Description
<query name>	The name that is given to the query expression. Multiple query name and expression combinations can be expressed, separated by a comma.
<expression>	The name of a table column or an expression.

Outputs

The WITH clause has the following outputs:

Table 2. WITH clause outputs
Output	Description
`ROWS`	Returns the complete set of rows that result from the query.
`COUNT`	Returns the number of rows that are returned by the query.
`ERROR: Not Supported`	This usage is not currently supported in the system.

Usage

The following provides sample usage.

To use the WITH clause when inserting:

MYDB.SCH1(USER)=> INSERT INTO emp_copy WITH employee AS (select * from 
emp) SELECT * FROM employee;

To use the WITH clause when updating:

MYDB.SCH1(USER)=> UPDATE emp_copy SET grp = 'gone' WHERE id = 
(WITH employee AS (select * from emp) SELECT id FROM employee WHERE id 
= 1);

To use the WITH clause when deleting:

MYDB.SCH1(USER)=> DELETE FROM emp_copy WHERE id IN 
(WITH employee AS (SELECT * FROM emp_copy where grp = 'gone')     
SELECT id FROM employee);

To use the WITH clause to run multiple subqueries in multiple clauses in a SELECT statement.

WITH    manager (mgr_id, mgr_name, mgr_dept) AS 
   (SELECT id, name, grp
    FROM emp_copy
    WHERE mgr = id AND grp != 'gone'), 
employee (emp_id, emp_name, emp_mgr) AS 
   (SELECT id, name, mgr_id
    FROM emp_copy JOIN manager ON grp = mgr_dept),
mgr_cnt (mgr_id, mgr_reports) AS
    (SELECT mgr, COUNT (*)
    FROM emp_copy
    WHERE mgr != id
    GROUP BY mgr)
SELECT *
FROM employee JOIN manager ON emp_mgr = mgr_id JOIN mgr_cnt
WHERE emp_id != mgr_id
ORDER BY mgr_dept;