CTE : Common Table Expressions
Présentation :
Les Common Table Expressions (CTE) sont des tables temporaires créées dynamiquement dans la requête SQL. Elles ne sont pas persistantes, elles n’existent qu’au sein des requêtes qui les définissent.
Les CTE sont particulièrement utiles pour :
- Apporter du confort aux développeurs lors de la maintenance car elles décomposent les requêtes compliquées en sous requêtes plus faciles à comprendre et à tester.
- Apporter de la lisibilité aux requêtes puisqu’elles sont les résultats de sous requêtes.
- Eviter la redondance de fonctions (formules de calcul par exemple) car le champ résultat portée par la CTE est nommé et peut donc être utilisé sans avoir à réécrire la fonctions déjà définie.
- D’alimenter une table locale à partir de données extraite d’un système distant (voir détail ici)
Syntaxe :
WITH nom_de_la_CTE AS ( Clause SELECT extrayant les données alimentant la CTE )
Clause SQL utilisant la CTE nom_de_la_CTE
Exemples de CTE :
Voici un exemple très simple de CTE permettant d’utiliser le résultat d’une formule sans avoir à la réécrire plusieurs fois.
Table 1 : Table des libellés des traitements LIB_TRAITEMENTS
Table 2 : Table des traitements TB_TRAITEMENTS
Requête sans CTE permettant d’extraire la liste des traitements ayant duré plus de 2 heures, triés par la durée de traitement du plus long au plus court :
SELECT TRT_LIB AS TRAITEMENT, TRT_NUM AS NUMERO,
VARCHAR_FORMAT(TRT_DEBUT, ‘HH24:MI:SS’) AS DEBUT,
VARCHAR_FORMAT(TRT_FIN, ‘HH24:MI:SS’) AS FIN,
VARCHAR_FORMAT(ADD_SECONDS(‘0001-01-01 00:00:00.000000’,
TIMESTAMPDIFF(2, CHAR(TRT_FIN – TRT_DEBUT))), ‘HH24:MI:SS’) AS DUREE
FROM TB_TRAITEMENTS A
INNER JOIN LIB_TRAITEMENTS B
ON B.TRT_CODE = A.TRT_CODE
WHERE VARCHAR_FORMAT(ADD_SECONDS(‘0001-01-01 00:00:00.000000’,
TIMESTAMPDIFF(2, CHAR(TRT_FIN – TRT_DEBUT))), ‘HH24:MI:SS’) > ’02:00:00′
ORDER BY VARCHAR_FORMAT(ADD_SECONDS(‘0001-01-01 00:00:00.000000’,
TIMESTAMPDIFF(2, CHAR(TRT_FIN – TRT_DEBUT))), ‘HH24:MI:SS’) DESC;
La formule de calcul de la durée est répétée 3 fois :
- Dans la clause SELECT
- Dans la clause WHERE
- Dans la clause ORDER BY
Le résultat obtenu est :
Voici une requête donnant le même résultat mais utilisant une CTE.
WITH CTE_TRAITEMENTS AS
(
SELECT TRT_CODE, TRT_NUM,
VARCHAR_FORMAT(TRT_DEBUT, ‘HH24:MI:SS’) AS DEBUT,
VARCHAR_FORMAT(TRT_FIN, ‘HH24:MI:SS’) AS FIN,
VARCHAR_FORMAT(ADD_SECONDS(‘0001-01-01 00:00:00.000000’,
TIMESTAMPDIFF(2, CHAR(TRT_FIN – TRT_DEBUT))), ‘HH24:MI:SS’) AS DUREE
FROM TB_TRAITEMENTS
)
SELECT TRT_LIB AS TRAITEMENT, TRT_NUM AS NUMERO, DEBUT, FIN, DUREE
FROM CTE_TRAITEMENTS A
INNER JOIN LIB_TRAITEMENTS B
ON B.TRT_CODE = A.TRT_CODE
WHERE DUREE > ’02:00:00′
ORDER BY DUREE DESC;
La formule de calcul de la durée de traitement n’est présente qu’une seule fois dans la clause WHERE de définition de la CTE.
Les clauses WHERE et ORDER BY utilisent maintenant le champ DUREE de la CTE.
Résultat :
La clause WHERE et la clause ORDER BY peuvent également, suivant les besoins, être portées par la CTE.
Voici un autre exemple dans lequel la CTE est utilisée dans un CREATE TABLE permettant de créer une table résultat :
CREATE TABLE QTEMP.RESULTATS AS
(
WITH CTE_TRAITEMENTS AS
(
SELECT TRT_CODE, TRT_NUM,
VARCHAR_FORMAT(TRT_DEBUT, ‘HH24:MI:SS’) AS DEBUT,
VARCHAR_FORMAT(TRT_FIN, ‘HH24:MI:SS’) AS FIN,
VARCHAR_FORMAT(ADD_SECONDS(‘0001-01-01 00:00:00.000000’,
TIMESTAMPDIFF(2, CHAR(TRT_FIN – TRT_DEBUT))), ‘HH24:MI:SS’) AS DUREE
FROM TB_TRAITEMENTS
)
SELECT TRT_LIB AS TRAITEMENT, TRT_NUM AS NUMERO, DEBUT, FIN, DUREE
FROM CTE_TRAITEMENTS A
INNER JOIN LIB_TRAITEMENTS B
ON B.TRT_CODE = A.TRT_CODE
WHERE DUREE > ’02:00:00′
ORDER BY DUREE DESC)
WITH DATA;
Un autre exemple dans lequel la CTE est utilisée dans une clause INSERT INTO :
INSERT INTO QTEMP.RESULTATS
WITH CTE_TRAITEMENTS AS
(
SELECT TRT_CODE, TRT_NUM,
VARCHAR_FORMAT(TRT_DEBUT, ‘HH24:MI:SS’) AS DEBUT,
VARCHAR_FORMAT(TRT_FIN, ‘HH24:MI:SS’) AS FIN,
VARCHAR_FORMAT(ADD_SECONDS(‘0001-01-01 00:00:00.000000’,
TIMESTAMPDIFF(2, CHAR(TRT_FIN – TRT_DEBUT))), ‘HH24:MI:SS’) AS DUREE
FROM TB_TRAITEMENTS
)
SELECT TRT_LIB AS TRAITEMENT, TRT_NUM AS NUMERO, DEBUT, FIN, DUREE
FROM CTE_TRAITEMENTS A
INNER JOIN LIB_TRAITEMENTS B
ON B.TRT_CODE = A.TRT_CODE
WHERE DUREE > ’02:00:00′
ORDER BY DUREE DESC;
