SQL_CYCLE

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