Catégories :

Syntaxe de requête

CONNECT BY

Joint une table à elle-même pour traiter les données hiérarchiques de la table. La sous-clause CONNECT BY de la clause FROM se répète pour traiter les données.

Par exemple, vous pouvez créer une requête qui montre un « éclatement de nomenclature » pour répertorier de manière récursive un composant et les sous-composants de ce composant.

La syntaxe Snowflake pour CONNECT BY est principalement compatible avec la syntaxe Oracle.

Voir aussi :

WITH

Syntaxe

La forme générale d’une instruction avec CONNECT BY est similaire à celle-ci (certaines variations dans l’ordre sont autorisées mais ne sont pas affichées) :

SELECT <column_list> [ , <level_expression> ]
  FROM <data_source>
    START WITH <predicate>
    CONNECT BY [ PRIOR ] <col1_identifier> = [ PRIOR ] <col2_identifier>
           [ , [ PRIOR ] <col3_identifier> = [ PRIOR ] <col4_identifier> ]
           ...
  ...
Copy
column_list

Cela suit généralement les règles de la clause de projection d’une instruction SELECT.

level_expression

Les requêtes CONNECT BY autorisent certaines pseudo-colonnes. Une de ces pseudo-colonnes est LEVEL, qui indique le niveau actuel de la hiérarchie (où le niveau 1 représente le sommet de la hiérarchie). La clause de projection de la requête peut utiliser LEVEL en tant que colonne.

data_source

La source de données est généralement une table, mais peut être une autre source de données semblable à une table, telle qu’une vue, UDTF, etc.

predicate

Le prédicat est une expression qui sélectionne le premier « niveau » de la hiérarchie (par exemple, le président de la société ou le composant de niveau supérieur dans un éclatement de nomenclature). Le prédicat doit ressembler à une clause WHERE, mais sans le mot clé WHERE.

Consultez la section Exemples (dans cette rubrique) pour obtenir des exemples de prédicats.

colN_identifier

La clause CONNECT BY doit contenir une ou plusieurs expressions similaires à celles utilisées dans les jointures. Plus précisément, une colonne du niveau « actuel » de la table doit faire référence à une colonne du niveau « antérieur » (supérieur) de la table.

Par exemple, dans une hiérarchie gestionnaire/employé, la clause peut ressembler à ceci :

... CONNECT BY manager_ID = PRIOR employee_ID ...
Copy

Le mot clé PRIOR indique que la valeur doit provenir du niveau antérieur (supérieur/parent).

Dans cet exemple, le manager_ID de l’employé actuel doit correspondre au employee_ID du niveau précédent.

La clause CONNECT BY peut contenir plusieurs expressions de ce type, par exemple :

... CONNECT BY y = PRIOR x AND b = PRIOR a ...
Copy

Chaque expression similaire à la suivante devrait avoir exactement une occurrence du mot clé PRIOR :

CONNECT BY <col_1_identifier> = <col_2_identifier>
Copy

Le mot clé PRIOR peut se trouver à gauche ou à droite du signe =. Par exemple :

CONNECT BY <col_1_identifier> = PRIOR <col_2_identifier>
Copy

ou

CONNECT BY PRIOR <col_1_identifier> = <col_2_identifier>
Copy

Notes sur l’utilisation

  • Une clause CONNECT BY joint toujours une table à elle-même, pas à une autre table.

  • Certaines variations dans la clause de projection sont valides. Bien que la syntaxe indique que level_expression se produit après la column_list, la ou les expressions de niveau peuvent apparaître dans n’importe quel ordre.

  • Le mot clé PRIOR doit apparaître exactement une fois dans chaque expression CONNECT BY. PRIOR peut apparaître du côté gauche ou du côté droit de l’expression, mais pas des deux.

  • Une requête avec CONNECT BY peut également contenir l’un ou les deux éléments suivants :

    • Filtre dans une clause WHERE.

    • JOINs (qui peut se trouver soit dans une clause FROM, soit dans une clause WHERE).

    L’ordre d’évaluation est le suivant :

    1. JOINs (qu’il soit spécifié dans la clause WHERE ou dans la clause FROM).

    2. CONNECT BY

    3. Filtres (autres que les filtres JOIN).

    Par exemple, les filtres d’une clause WHERE sont traités après CONNECT BY.

  • L’implémentation Snowflake de CONNECT BY est principalement compatible avec l’implémentation Oracle. Cependant, Snowflake ne prend pas en charge :

    • NOCYCLE

    • CONNECT_BY_ISCYCLE

    • CONNECT_BY_ISLEAF

  • Snowflake prend en charge la fonction SYS_CONNECT_BY_PATH lorsqu’elle est utilisée avec la clause CONNECT BY. SYS_CONNECT_BY_PATH renvoie une chaîne contenant le chemin d’accès de la racine à l’élément actuel. Un exemple est inclus dans la section Exemples ci-dessous.

  • Snowflake prend en charge l’opérateur CONNECT_BY_ROOT lorsqu’il est utilisé avec la clause CONNECT BY. L’opérateur CONNECT_BY_ROOT permet au niveau actuel d’utiliser les informations du niveau racine de la hiérarchie, même si le niveau racine n’est pas le parent immédiat du niveau actuel. Un exemple est inclus dans la section Exemples ci-dessous.

  • La clause CONNECT BY peut itérer autant de fois que nécessaire pour traiter les données. La construction incorrecte d’une requête peut provoquer une boucle infinie. Dans ce cas, la requête continue de s’exécuter jusqu’à ce qu’elle réussisse, qu’elle expire (c’est-à-dire qu’elle dépasse le nombre de secondes spécifié par le paramètre STATEMENT_TIMEOUT_IN_SECONDS) ou que vous annuliez la requête.

    Pour plus d’informations sur la façon dont les boucles infinies peuvent se produire et pour des instructions sur la façon d’éviter ce problème, voir Dépannage d’une CTE récursive.

Exemples

Cet exemple utilise un CONNECT BY pour afficher la hiérarchie de gestion dans une table d’informations sur les employés. La table et les données sont indiquées ci-dessous :

CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);
Copy
INSERT INTO employees (title, employee_ID, manager_ID) VALUES
    ('President', 1, NULL),  -- The President has no manager.
        ('Vice President Engineering', 10, 1),
            ('Programmer', 100, 10),
            ('QA Engineer', 101, 10),
        ('Vice President HR', 20, 1),
            ('Health Insurance Analyst', 200, 20);
Copy

La requête et la sortie sont indiquées ci-dessous :

SELECT employee_ID, manager_ID, title
  FROM employees
    START WITH title = 'President'
    CONNECT BY
      manager_ID = PRIOR employee_id
  ORDER BY employee_ID;
+-------------+------------+----------------------------+
| EMPLOYEE_ID | MANAGER_ID | TITLE                      |
|-------------+------------+----------------------------|
|           1 |       NULL | President                  |
|          10 |          1 | Vice President Engineering |
|          20 |          1 | Vice President HR          |
|         100 |         10 | Programmer                 |
|         101 |         10 | QA Engineer                |
|         200 |         20 | Health Insurance Analyst   |
+-------------+------------+----------------------------+
Copy

Cet exemple utilise la fonction SYS_CONNECT_BY_PATH pour afficher la hiérarchie du président au salarié actuel :

SELECT SYS_CONNECT_BY_PATH(title, ' -> '), employee_ID, manager_ID, title
  FROM employees
    START WITH title = 'President'
    CONNECT BY
      manager_ID = PRIOR employee_id
  ORDER BY employee_ID;
+----------------------------------------------------------------+-------------+------------+----------------------------+
| SYS_CONNECT_BY_PATH(TITLE, ' -> ')                             | EMPLOYEE_ID | MANAGER_ID | TITLE                      |
|----------------------------------------------------------------+-------------+------------+----------------------------|
|  -> President                                                  |           1 |       NULL | President                  |
|  -> President -> Vice President Engineering                    |          10 |          1 | Vice President Engineering |
|  -> President -> Vice President HR                             |          20 |          1 | Vice President HR          |
|  -> President -> Vice President Engineering -> Programmer      |         100 |         10 | Programmer                 |
|  -> President -> Vice President Engineering -> QA Engineer     |         101 |         10 | QA Engineer                |
|  -> President -> Vice President HR -> Health Insurance Analyst |         200 |         20 | Health Insurance Analyst   |
+----------------------------------------------------------------+-------------+------------+----------------------------+
Copy

Cet exemple utilise le mot clé CONNECT_BY_ROOT pour afficher les informations du haut de la hiérarchie dans chaque ligne de la sortie :

SELECT 
employee_ID, manager_ID, title,
CONNECT_BY_ROOT title AS root_title
  FROM employees
    START WITH title = 'President'
    CONNECT BY
      manager_ID = PRIOR employee_id
  ORDER BY employee_ID;
+-------------+------------+----------------------------+------------+
| EMPLOYEE_ID | MANAGER_ID | TITLE                      | ROOT_TITLE |
|-------------+------------+----------------------------+------------|
|           1 |       NULL | President                  | President  |
|          10 |          1 | Vice President Engineering | President  |
|          20 |          1 | Vice President HR          | President  |
|         100 |         10 | Programmer                 | President  |
|         101 |         10 | QA Engineer                | President  |
|         200 |         20 | Health Insurance Analyst   | President  |
+-------------+------------+----------------------------+------------+
Copy

Cet exemple utilise un CONNECT BY pour afficher un « éclatement de nomenclature » :

Voici les données :

-- The components of a car.
CREATE TABLE components (
    description VARCHAR,
    quantity INTEGER,
    component_ID INTEGER,
    parent_component_ID INTEGER
    );

INSERT INTO components (description, quantity, component_ID, parent_component_ID) VALUES
    ('car', 1, 1, 0),
       ('wheel', 4, 11, 1),
          ('tire', 1, 111, 11),
          ('#112 bolt', 5, 112, 11),
          ('brake', 1, 113, 11),
             ('brake pad', 1, 1131, 113),
       ('engine', 1, 12, 1),
          ('piston', 4, 121, 12),
          ('cylinder block', 1, 122, 12),
          ('#112 bolt', 16, 112, 12)   -- Can use same type of bolt in multiple places
    ;
Copy

Voici la requête et la sortie :

SELECT
  description,
  quantity,
  component_id, 
  parent_component_ID,
  SYS_CONNECT_BY_PATH(component_ID, ' -> ') AS path
  FROM components
    START WITH component_ID = 1
    CONNECT BY 
      parent_component_ID = PRIOR component_ID
  ORDER BY path
  ;
+----------------+----------+--------------+---------------------+----------------------------+
| DESCRIPTION    | QUANTITY | COMPONENT_ID | PARENT_COMPONENT_ID | PATH                       |
|----------------+----------+--------------+---------------------+----------------------------|
| car            |        1 |            1 |                   0 |  -> 1                      |
| wheel          |        4 |           11 |                   1 |  -> 1 -> 11                |
| tire           |        1 |          111 |                  11 |  -> 1 -> 11 -> 111         |
| #112 bolt      |        5 |          112 |                  11 |  -> 1 -> 11 -> 112         |
| brake          |        1 |          113 |                  11 |  -> 1 -> 11 -> 113         |
| brake pad      |        1 |         1131 |                 113 |  -> 1 -> 11 -> 113 -> 1131 |
| engine         |        1 |           12 |                   1 |  -> 1 -> 12                |
| #112 bolt      |       16 |          112 |                  12 |  -> 1 -> 12 -> 112         |
| piston         |        4 |          121 |                  12 |  -> 1 -> 12 -> 121         |
| cylinder block |        1 |          122 |                  12 |  -> 1 -> 12 -> 122         |
+----------------+----------+--------------+---------------------+----------------------------+
Copy
Langage: Français