IBM PureData System for Analytics, Version 7.1

Clause WITH

La clause WITH permet d'améliorer la vitesse des requêtes pour les sous-requêtes complexes, sans qu'aucune conversion ne soit requise. On parle également de factorisation des sous-requêtes, qui est utilisée lorsqu'une sous-requête est démarrée plusieurs fois.

La syntaxe de la clause WITH lui permet d'être utilisée chaque fois que la syntaxe SELECT était acceptable par le passé (INSERT, UPDATE, DELETE, CTAS et SELECT).

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

Pour pouvoir rétromigrer vers une version de système IBM® Netezza qui ne prend pas en charge 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 d'utilisation de la clause WITH :

<expression de requête > ::=
    [ <clause WITH>] <corps de l'expression de requête>;
<liste with> ::=
      <élément de liste with> [ { <virgule> <élément de liste with> }… ]

<élément de liste with> ::=
      <nom de requête> [ <parenthèse gauche> <liste de colonnes with> 
<parenthèse droite> ]
         AS <parenthèse gauche> <expression de requête> <parenthèse droite> 
[ <clause search ou cycle> ]

<liste de colonnes with> ::= <liste de noms de colonne>

<corps d'expression de requête> ::=
      <terme de requête>
      | <corps d'expression de requête> UNION [ ALL | DISTINCT ]
         [ <spéc correspondante> ] <terme de requête>
      | <corps d'expression de requête> EXCEPT [ ALL | DISTINCT ]
         [ <spéc correspondante> ] <terme de requête>

<terme de requête > ::=
      <principal de la requête>
      | <terme de requête> INTERSECT [ ALL | DISTINCT ]
         [ <spéc correspondante> ] <principal de la requête>

<principal de la requête> ::=
      <table simple>
      | <parenthèse gauche> <corps d'expression de requête> <parenthèse droite>

<table simple> ::=
<spécification de requête>
| <constructeur de valeur de table>
| <table explicite>

<table explicite> ::= TABLE <nom de table ou de requête>

<spéc correspondante> ::=
      CORRESPONDING [ BY <parenthèse gauche> <liste de colonnes correspondantes> 
<parenthèse droite> ]

<liste de colonnes correspondantes> ::= <liste de noms de colonne>

Entrées

La clause WITH accepte les entrées suivantes :

Tableau 1. Entrées de la clause WITH
Entrée	Description
<nom de requête>	Nom donné à l'expression de requête. Il est possible d'exprimer plusieurs combinaisons de nom de requête et d'expression, en les séparant par une virgule.
<expression>	Nom d'une colonne de table ou d'une expression.

Sorties

La clause WITH génère les sorties suivantes :

Tableau 2. Sorties de la clause WITH
Sortie	Description
`ROWS`	Renvoie l'intégralité des lignes qui résultent 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 dans le système.

Syntaxe

Voici un exemple de syntaxe :

Pour utiliser la clause WITH lors d'une insertion :

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

Pour utiliser la clause WITH lors d'une 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 d'une 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;