| 
Rappel sur les Requêtes récursives en V5R40 ------------------------------------------- Soit le fichier, décrivant des vols d'avions, suivant : CREATE TABLE FLIGHTS (DEPARTURE CHAR(20), ARRIVAL CHAR(20), CARRIER CHAR(15), FLIGHT_NUMBER CHAR(5), PRICE INT) la requête suivante permet de voir toutes les destinations au départ de Chicago : WITH temp (departure , arrival) as ( SELECT departure, arrival from flights where departure = 'Chicago' UNION ALL SELECT f.departure, f.arrival from temp T join flights F on t.arrival = f.departure ) SELECT * FROM temp |
|
Affiche ....+....1....+....2....+....3.... DEPARTURE ARRIVAL Chicago Miami Chicago Frankfurt Miami Lima Frankfurt Moscow Frankfurt Beijing Frankfurt Vienna Moscow Tokyo Tokyo Hong Kong vous pouvez obtenir désormais la même chose sur une syntaxe plus simple : SELECT departure, arrival FROM vol.flights START WITH departure = 'Chicago' CONNECT BY PRIOR arrival = departure; cette syntaxe est nommée requête hiérarchique et offre quelques différences: |
|
la requête basée sur une CTE (common Table Expression : clause WITH) traite par défaut, les liens par niveau, la requête hiérarchique, par branche DEPARTURE ARRIVAL Chicago Miami Miami Lima Chicago Frankfurt Frankfurt Vienna Frankfurt Beijing Frankfurt Moscow Moscow Tokyo Tokyo Hong Kong cela est plus clair avec une colonne LEVEL, qui est aussi plus simple à produire avec les requêtes hiérarchiques : SELECT departure, arrival, LEVEL as niveau FROM flights START WITH departure = 'Chicago' CONNECT BY PRIOR arrival = departure |
|
DEPARTURE ARRIVAL NIVEAU Chicago Miami 1 Miami Lima 2 Chicago Frankfurt 1 Frankfurt Vienna 2 Frankfurt Beijing 2 Frankfurt Moscow 2 Moscow Tokyo 3 Tokyo Hong Kong 4 la CTE aurait affiché 1/1 puis 2/2/2/2 enfin 3 et 4, sauf à utiliser : SEARCH DEPTH FIRST BY (un nom de colonne) SET nouvelle-colonne Indiquez 1/ un nom de colonne dont il faut analyser TOUTE la profondeur 2/ un nom de colonne virtuelle à créér, ce flag interne sert à SQL pour savoir s'il a déja traité une ligne et DOIT être le critère de tri (ORDER BY) sur la requête finale |
|
WITH temp (departure , arrival, niveau ) as ( SELECT departure, arrival, 1 from flights where departure = 'Chicago' UNION ALL SELECT f.departure, f.arrival, t.niveau + 1) from temp T join flights F on t.arrival = f.departure ) SEARCH DEPTH FIRST BY arrival SET tri Select * From Temp ORDER BY tri les requêtes hiérarchiques proposent en plus un tri, pour les lignes "soeurs" (siblings, ayant les mêmes parents). SELECT CONNECT_BY_ROOT departure AS origin, departure, arrival, LEVEL niveau, price prix FROM flights START WITH departure = 'New York' CONNECT BY PRIOR arrival = departure ORDER SIBLINGS BY price ASC classe, pour les trajets ayant la même origine, par prix croissant |
|
ORIGIN DEPARTURE ARRIVAL NIVEAU PRIX 1 New York New York Los Angeles 1 330 2 New York Los Angeles Tokyo 2 530 3 New York Tokyo Hong Kong 3 330 4 New York New York London 1 350 5 New York London Athens 2 340 6 New York Athens Nicosia 3 280 7 New York New York Paris 1 400 8 New York Paris Rome 2 340 9 New York Paris Madrid 2 380 10 New York Paris Cairo 2 480 vous remarquerez que le tri principal se fait suivant l'arborescence (New York->Los Angeles->Tokyo->Hong Kong sur les trois premières) ensuite, que les lignes 1 , 4 et 7 (même origine new-york) apparaissent selon leur prix, ainsi que les lignes 8,9 et 10 (origine Paris) |
|
A noter que la CTE suivante propose un service que ne sait pas rendre une requête hierarchique, le cumul des prix : WITH temp (departure , arrival, niveau , total ) as ( SELECT departure, arrival, 1 , price from flights where departure = 'Chicago' UNION ALL SELECT f.departure, f.arrival, t.niveau + 1, total + price from temp T join flights F on t.arrival = f.departure ) select * from temp DEPARTURE ARRIVAL NIVEAU TOTAL Chicago Miami 1 300 Chicago Frankfurt 1 480 Miami Lima 2 830 Frankfurt Moscow 2 1.060 Frankfurt Beijing 2 960 Frankfurt Vienna 2 680 Moscow Tokyo 3 1.740 |
|
La clause CYCLE , évitait les boucles infinies avec les CTE CYCLE (nom de colonne) SET vartemp = valeur1 DEFAULT valeur0 quand SQL va se rendre compte qu'il boucle (une ligne déja vue) il va attribuer à vartemp (nouvelle variable interne) la valeur "valeur1" (dans les autres cas, elle contient "valeur0") la ligne va quand même être affichée, mais la boucle se termine Avec CONNECT BY, la boucle infinie est automatiquement détectée et signalée par SQ20451 : CYCLE DETECTED IN HIERARCHICAL QUERY. la requête se termine de manière anticipée (en erreur), sauf à indiquer CONNECT BY NOCYCLE PRIOR arrival = departure la ligne provoquant la boucle sera affichée et la boucle interrompue. |
|
la pseudo variable CONNECT_BY_ISCYCLE indique par 1 (oui) ou 0 (non) si une ligne provoque une boucle. pour éventuellement, filtrer suite à CONNECT BY NOCYCLE la pseudo variable CONNECT_BY_ISLEAF indique par 1 (oui) ou 0 (non) si une ligne est la dernière (une feuille de l'arborescence) une ville n'ayant pas de destination, par exemple. enfin la pseudo variable LEVEL que nous avons vu précédement indique le niveau hiérarchique d'une ligne dans l'arborescence (à partir de 1) une astuce peut consister, à l'affichage, à utiliser SPACE(LEVEL) ou SPACE(LEVEL * 3), afin d'avoir un affichage incrémenté. |
|
Outre l'opérateur PRIOR, vu plus haut et valide uniquement dans la clause CONNECT BY, deux autre opérateurs sont bien pratiques : CONNECT_BY_ROOT <expression> retourne la valeur de la racine de <expression> pour une branche par exemple SELECT CONNECT_BY_ROOT departure AS Origine, retourne la ville de départ (New York, si START WITH departure = 'New York') SYS_CONNECT_BY_PATH( <expression1> , <expression2>) retourne le chemin ayant permis d'arriver à cette ligne en concatenant toutes les valeurs de <expression1> , séparées par <expression2> par exemple SELECT SYS_CONNECT_BY_PATH(trim(departure), '/') AS chemin retourne sous forme de CLOB : /Chicago /Chicago/Miami /Chicago/Frankfurt /Chicago/Frankfurt/Moscow /Chicago/Frankfurt/Moscow/Tokyo |
|
Pour terminer, deux précisions on peut faire une requête hiérarchique basée sur plusieurs tables 1/ avec UNION FLIGHTS contient les vols d'avions, TRAINS les trajets en train et vous voulez déplacer sans tenir du moyen de locomotion ! SELECT CONNECT_BY_ROOT departure AS depart, arrival, LEVEL-1 correspondance FROM ( SELECT departure, arrival FROM flights UNION SELECT departure, arrival FROM trains ) as trajet START WITH departure = 'Chicago' CONNECT BY PRIOR arrival = departure |
|
on peut faire une requête hiérarchique basée sur plusieurs tables 2/ avec JOIN FLIGHTSTAT contient des statistiques par vol sur l'exactitude des horaires (en % de fois ou l'horaire a été respecté). SELECT CONNECT_BY_ROOT departure AS depart, arrival, on_time FROM flights JOIN flightstats ON FLIGHT_NUMBER = FLIGHTN START WITH departure = 'New York' CONNECT BY PRIOR arrival = departure permettant de faire une sélection (l'un des vols à une exactitude > à 90%) SELECT CONNECT_BY_ROOT departure AS depart, arrival, on_time FROM flights JOIN flightstats ON FLIGHT_NUMBER = FLIGHTN WHERE ON_TIME_PERCENT > 90 START WITH departure = 'New York' CONNECT BY PRIOR arrival = departure |
|
Si vous souhaitez que tous les trajets aient une exactitude des horaires supérieure à 90 % Ecrivez SELECT CONNECT_BY_ROOT departure AS depart, arrival, on_time_percent FROM ( SELECT departure , arrival, on_time_percent FROM flights JOIN flightstats ON FLIGHT_NUMBER = FLIGHTN where ON_TIME_PERCENT > 90 ) as t1 START WITH departure = 'New York' CONNECT BY PRIOR arrival = departure ou bien SELECT CONNECT_BY_ROOT departure AS depart, arrival, on_time FROM flights JOIN flightstats ON FLIGHT_NUMBER = FLIGHTN START WITH departure = 'New York' AND ON_TIME_PERCENT > 90 CONNECT BY PRIOR arrival = departure AND ON_TIME_PERCENT > 90 (aves nos données de test, le résultat est vide) |
|
En résumé, une requête hiérarchique utilise cette syntaxe : SELECT expression1 , expression2 FROM table START WITH colonne = 'racine' CONNECT BY [NOCYCLE] PRIOR enfant = parent Opérateurs CONNECT_BY_ROOT retourne la valeur racine pour une branche SYS_CONNECT_BY_PATH retourne la suite des valeurs, concaténées Pseudo variables LEVEL retourne le niveau dans la hiérarchie CONNECT_BY_ISCYCLE indique si une ligne provoque une boucle infinie CONNECT_BY_ISLEAF indique si une ligne est la dernière de sa branche |
