clause With

La clause WITH permet d'améliorer la vitesse des requêtes pour les sous-requêtes complexes, sans qu'il soit nécessaire de procéder à une conversion. Cette méthode, également appelée factorisation des sous-requêtes, est utilisée lorsqu'une sous-requête est lancée plusieurs fois.

La syntaxe de la clause WITH permet de l'utiliser partout où la syntaxe SELECT était acceptable dans le passé (INSERT, UPDATE, DELETE, CTAS et SELECT).

Les requêtes récursives pour la clause WITH ne sont pas prises en charge.

Avant de passer à une version du système Netezza Performance Server qui ne supporte pas la syntaxe de la clause WITH, tous les objets SQL (vues et procédures stockées) qui utilisent cette nouvelle syntaxe doivent être supprimés du système.

Syntaxe

Syntaxe pour l'utilisation de la clause WITH :

<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>

Entrées

La clause WITH prend en compte les éléments suivants :

Tableau 1. Entrées de la clause WITH
Entrée	Descriptif
<nom de la requête>	Nom donné à l'expression de la requête. Plusieurs combinaisons de noms de requêtes et d'expressions peuvent être exprimées, séparées par une virgule.
<expression>	Le nom d'une colonne du tableau ou d'une expression.

Sorties

La clause WITH a les résultats suivants :

Tableau 2. La clause WITH produit
Sortie	Descriptif
`ROWS`	Renvoie l'ensemble des lignes résultant de la requête.
`COUNT`	Renvoie le nombre de lignes renvoyées par la requête.
`ERROR: Not Supported`	Cette utilisation n'est actuellement pas prise en charge par le système.

Utilisation

Voici un exemple d'utilisation.

Pour utiliser la clause WITH lors de l'insertion :

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

Pour utiliser la clause WITH lors de la mise à jour :

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

Pour utiliser la clause WITH lors de la suppression :

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

Pour utiliser la clause WITH afin d'exécuter plusieurs sous-requêtes dans plusieurs clauses d'une instruction SELECT.

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;