cpycours

 

SQL/400 Interactif. (DML, 2/2)

 

 
 SQL, Niveau Avancé

  

 
 
 + CASE : condition à la réalisation d'une expression 
   
        Exemple  : 
   



            SELECT MATRICULE, NOM,                           
                                                             
                   CASE  SUBSTR(SERVICE, 1, 3)               
                                                             
                         WHEN 'INF' THEN 'INFORMATIQUE'      
                         WHEN 'PER' THEN 'PERSONNEL'         
                         WHEN 'PRO' THEN 'PRODUCTION'        
                         WHEN 'COM' THEN 'COMMERCIAL'        
                         ELSE 'Divers ....'                  
                    END                                      
                                                             
              FROM personnel  WHERE ...                      
                                                            

 

 

on peut indiquer la colonne testée sur chaque clause WHEN

 

 

   
             SELECT CODART, LIBART, PRIX,                      
                    CASE                                       
                    WHEN   PRIX < 0      THEN 'prix négatif'   
                    WHEN   CODART = 1557 THEN 'pas de prix'    
                      ELSE   'prix normal' END                   
                                                               
             FROM  ...   WHERE ...                             
   
   
  
 et dans la sélection (WHERE)


   
     SELECT * FROM COMMANDES WHERE                                         
              (CASE WHEN PRIXMOYEN = 0 THEN PRIXTARIF * QTE                
                    ELSE                    PRIXMOYEN * QTE END)  > 10000  
   
   
 
     le test peut être IS NULL / IS NOT NULL  
     la valeur retournée (THEN|ELSE) peut être le mot réservé NULL 


 Autre exemple avec CASE, il faut compter, par client, le nombre de réglement
                             par type  (chèque, espèce et CB) 
   
    .     Il existe dans l'entête de facture, un code réglement 'CH', 'ES', 'CB' : 
    
                                                                           
       Select nocli ,                                                        
                                                                           
          sum(CASE cdreglement when 'CH' then 1 else 0 END) as CHEQUE ,    
          sum(CASE cdreglement when 'ES' then 1 else 0 END) as ESPECE ,    
          sum(CASE cdreglement when 'CB' then 1 else 0 END) as CARTE       
                                                                           
      from factures GROUP BY nocli                                        

 

 

 
  +   SOUNDEX(colonne)    fourni un algoritme retournant une représentation   phonétique de "colonne" (surtout les noms propres) 
                             permettant de meilleurs comparaisons 
   
      Ce codage a été utilisé aux états-unis lors de recensements 
   
      voir  http://www.bradandkathy.com/genealogy/overviewofsoundex.html
     
   
    et s'utilise sous la forme : 
   
   
            WHERE SOUNDEX(NOM) = SOUNDEX('HENRI')       [ici, H560] 
   
   
  l'algorithme est le suivant : 
   
   
     1/ conversion en majuscule non accentuée 
  

    2/ conservation du premier caractère (dans notre cas le H)


     3/ suppression de toutes les occurences des lettres dont la liste suit   (voyelles) : 
   
             A, E, H, I, O, U, W, Y  (W est un problème en Français)
   
   
     4/ attribution d'un code numérique, suivant le type de lettre 
        (labiale, dentale, sifflante, chuintante, ....) 
   
        1 pour B F V P                   4 pour L 
        2  "   C G J K Q S X Z           5  "   M N 
        3  "   D T                       6  "   R 
   
     (cette série est donnée pour l'anglo-américain, pour l'adaptation
         Française voir  http://www.chez.com/algor/soundex/soundex.htm  ) 
   
 
     5/ élimination de toutes les paires consécutives de chiffres dupliqués (RR = R) 
   
     6/ ne conserver de la chaîne que les quatres premières positions  (complétées par des "0", si besoin)

 
       sous la forme Lccc   L étant la première lettre [ cf 2/ ] 

                             ccc étant le résultat produit en 6/ 
   
   
     ainsi SOUNDEX('troi')  retourne T600 , SOUNDEX('Troye') aussi. 
   
     mais  SOUNDEX('trois') retourne T620 
                                  ce qui, en français, est un problème. 
   
   Rochester précise qu'il n'y a pas d'adaptation langage de l'algorithme. 
   
     DIFFERENCE(col1 , col2) indique la différence phonétique (de 0 à 4,basée sur SOUNDEX), entre les deux arguments. 
   
                             0: les deux arguments sont trés différents 
                             4: ils sont phonétiquement très proches. 

     ->

 

XML  



Vous trouverez désormais (V7) de nombreuses fonctions pour produire du XML

 


FETCH / LIMIT

   V5R10, l'ordre SELECT peut-être complété par : 
   
     FETCH FIRST x ROWS ONLY  
 
  qui indique à SQL de ne retourner que les "x" premières lignes.  Très pratique pour obtenir les meilleurs scores (Top 10, Top 100, ...) 
   
      select codart , count(*) from commandes 
       group by codart 
      order by 2 
       FETCH FIRST 50 ROWS ONLY 
  
   vous donne les 50 articles les moins commandés (en nb de commandes) 

      select codart , count(*) from commandes 
       group by codart 
      order by 2 DESC 
       FETCH FIRST 50 ROWS ONLY 
  
   vous donne les 50 articles les plus commandés. 



   V7R2, l'ordre SELECT peut-être complétés par : 
   
       LIMIT n OFFSET Y    
   
   qui indique à SQL de ne retourner que les "n" premières lignes à partir Y+1.

  très pratique pour afficher par page (web)



   V7R3 (TR3), les ordres UPDATE / DELETE acceptent : 
   
       LIMIT n OFFSET Y    
 

 


UNION

+  SELECT UNION (ALL) 

   
               place le résultat d'un ordre SELECT à la suite d'un précedent. 
   
               les colonnes sélectionnées doivent être comparables pour toutes les requêtes .
   
                 les SELECT/UNION peuvent maintenant être mémorisés dans des vues 



               EX: soit la table des formateurs suivante :  (pour les stagiaires, voir SQL1)
   
   
 AGENCE 
 NOFORM 
 NOFORM 
 01 
 01 
 01 
 02 
 02 
 03 
 03 
 01 
 02 
 03 
 04 
 05 
 07 
 08 
 jean-claude 
 albert     
 dominique 
 marie 
 françois 
 christine   
 isabelle  
  

 

          SELECT 'nb de stagiaires', COUNT(*), AGENCE 
                  FROM SQLDB/STGTBL 
                   GROUP BY AGENCE 
   
   UNION ALL 
   
          SELECT 'nb de formateurs', COUNT(*), 
                  AGENCE FROM SQLDB/FRMTBL 
                  GROUP BY AGENCE 
     
                Donnera le nombre de formateurs par agence 
                 puis le nombre de stagiaires par agence 
   
                 (1er SELECT placé à la suite du 2ème) 
   
  

    
   
 COUNT 
 AGENCE 
 Nb de formateurs 
 Nb de formateurs 
 Nb de formateurs 
 Nb de stagiaires 
 Nb de stagiaires 
 Nb de stagiaires 
 03 
 02 
 02 
 35 
 25 
 25 
 01 
 02 
 03 
 01 
 02 
 03

  

   
   
     SI ALL n'est pas renseigné il n'y aura pas de ligne dupliquée. 
   
        (sont considérées comme lignes dupliquées, deux lignes venant de
            SELECT différents et dont TOUTES les colonnes ont le même contenu) 



           SELECT 'nb de stagiaires', COUNT(*), AGENCE 
                   FROM SQLDB/STGTBL 
                    GROUP BY AGENCE 
    UNION ALL 
           SELECT 'nb de formateurs', COUNT(*), 
                   AGENCE FROM SQLDB/FRMTBL 
                   GROUP BY AGENCE 
   Order by 3 
   


         Affiche : 
   
   
   
 COUNT 
 AGENCE 
 Nb de formateurs 
 Nb de stagiaires  
 Nb de formateurs 
 Nb de stagiaires  
 Nb de formateurs 
 Nb de stagiaires 
 03 
 35 
 02 
 25 
 02 
 25 
 01 
 01 
 02 
 02 
 03 
 03 
   
   
 L'ordre SQL SELECT complet (FULL SELECT) admet en V5R30 les opérateurs 
     INTERSECT et EXCEPT en plus de l'opérateur UNION ALL ou UNION DISTINCT 
   
   
   INTERSECT (ou INTERSECT DISTINCT, qui est identique) affiche les
     enregistrements qui se trouvent dans l'une ET l'autre des requêtes. 
   
   EXCEPT (ou EXCEPT DISTINCT, qui est identique) affiche les enregistrements
     qui se trouvent dans l'une ET PAS dans l'autre requête. 

  

    SELECT matricule, nom from personnel WHERE departement = 'INFO' 
      EXCEPT 
     SELECT matricule, nom from absence WHERE year(dateabs) =  year(now()) 
             and code = 'M' 
   
   Donne la liste des lignes qui sont dans la première requête uniquement. 
      ( les personnes du département INFO qui n'ont pas été absent cette année pour cause de maladie ) 
   
      INTERSECT donne la liste des lignes qui sont dans les deux requêtes

 

 

  récapitulatif, soit deux fichiers f1 et f2 : 
    
   f1
 
 f2 
     A
     B
     B
     C
     C
     D
     E
 
     A
     B
     B
     C
     F
     F   

   
      UNION
 
 UNION DISTINCT 
   INTERSECT 
 F1 EXCEPT F2 
   F2 EXCEPT F1
 
       A
       A
       B
       B
       B
       B
       C
       C
       C
       D
       E
       F
       F   
     A
     B
     C
     D
     E
     F 
    A
    B
    C 
     D
     E   
       F   
                    


 

OLAP


 + nouvelles fonctions OLAP, liées à la V5R40 : 
   
   
   ROW_NUMBER() OVER ()           numérote les lignes 

   ROW_NUMBER() OVER(order by xx) numérote les lignes sur un critère de tri 
   
      -> select row_number() over (order by nbr desc), 
              pr_code, nbr  from stat   
       

   
    Affiche :      
                  ROW_NUMBER          PR_CODE      NBR 
                           1            2.432       26 
                           2            2.455       25 
                           3            2.486       22 
         ... 
   
 

      ROW_NUMBER() OVER(PARTITION BY yy ORDER by xx) numérote les lignes à l'intérieur d'un groupe (yy) sur un critère de tri (xx) 

    -> select row_number() over (Partition by appel Order by nbr desc), 
              pr_code, appel, nbr  from stat   --numérotation par appellation
 

   
    DENSE_RANK() attribue un rang consécutif 
   
    RANK() attribue un rang non consécutif (en gérant les ex-aequo) 

     -> select rank() over (order by nbr desc)  , 
               pr_code, nbr  , dense_rank over (order by nbr desc) 
             from stat 
   
   
    Affiche :     
   
                 RANK          PR_CODE      NBR       DENSE_RANK 
                     1            2.432       26             1 
                     1            2.455       26             1 
                     3            2.486       22             2 
                     4            2.547       20             3 
                     4            6.390       20             3 
                     6            2.461       19             4 
                     6            4.321       19             4 
                     6            6.343       19             4 
   
   
   
    Dans tous les cas, le critère de tri à l'affichage (ORDER BY final)
        peut être différent du critère indiqué dans OVER.

 en V6, la clause GROUP BY évolue beaucoup pour implémenter aussi des fonctions OLAP

 

 

  Soit un fichier CAVE et un select GROUP BY "normal", sur deux critères : 
   
   SELECT year(entreele) , cav_format, sum(prxactuel) 
     FROM ma_cave join vins using(vin_code) 
    GROUP BY year(entreele) , cav_format 
   
  affichant 
   
       YEAR   CAV_FORMAT                      SUM  
      2.006   CAISSE de 12                  20,82 
      2.008   Bouteille                      5,35 
      2.007   Bouteille                    278,15 
      2.008   CAISSE de 12                  15,76 
      2.006   Bouteille                    278,78 
      2.007   CAISSE DE 6                   28,96 
   
   Le montant des vins par année et format de stockage (un niveau de rupture) 
   
    Nous allons ajouter les nouvelles clauses : GROUPING SETS 
                                                ROLLUP 
                                                CUBE 


  SELECT year(entreele) , cav_format, sum(prxactuel) 
    FROM ma_cave join vins using(vin_code) 
   GROUP BY GROUPING SETS (year(entreele) , cav_format) 
   
  affiche le total par année, PUIS le total par format 
   
       YEAR   CAV_FORMAT                      SUM  
      2.008   -                             21,11 
      2.006   -                            299,60 
      2.007   -                            307,11 
           -  CAISSE DE 6                   28,96 
           -  CAISSE de 12                  36,58 
           -  Bouteille                    562,28 
   
  Cette clause admet la syntaxe suivante: 
   
   SELECT year(entreele), MONTH(entreele), cav_format, sum(prxactuel) 
     FROM ma_cave join vins using(vin_code) 
    GROUP BY GROUPING SETS (  (year(entreele) , cav_format) , 
                              (year(entreele) , month(entreele))

                          )

 

 

       YEAR           MONTH   CAV_FORMAT             SUM  
      2.006                -  CAISSE de 12         20,82 
      2.008                -  Bouteille             5,35 
      2.007                -  Bouteille           278,15 
      2.008                -  CAISSE de 12         15,76 
      2.006                -  Bouteille           278,78 
      2.007                -  CAISSE DE 6          28,96 
      2.007               8   -                    15,59 
      2.006               7   -                    14,40 
      2.008               3   -                    15,76 
      2.007               3   -                    20,82 
      2.007               9   -                   155,48 
      2.007               2   -                    12,19 
      2.007               1   -                    28,96 
      2.006              11   -                    35,00 
      2.006               4   -                    20,82 
      2.007               6   -                    74,07 
      2.006               9   -                   229,38 
      2.008               5   -                     5,35

 

 

  SELECT year(entreele) , cav_format, sum(prxactuel) 
    FROM ma_cave join vins using(vin_code) 
   GROUP BY ROLLUP (year(entreele) , cav_format) 
   
 
  affiche le total par année/format, puis par année, puis le total général 
   
       YEAR   CAV_FORMAT                      SUM  
      2.006   Bouteille                    278,78 
      2.006   CAISSE de 12                  20,82 
      2.006   -                            299,60   <--
      2.007   Bouteille                    278,15 
      2.007   CAISSE DE 6                   28,96 
      2.007   -                            307,11   <--
      2.008   Bouteille                      5,35 
      2.008   CAISSE de 12                  15,76 
      2.008   -                             21,11 
          -   -                            627,82   <==
    
      Vous pourriez compléter le select par un ORDER BY 



  SELECT year(entreele) , cav_format, sum(prxactuel) 
    FROM ma_cave join vins using(vin_code) 
   GROUP BY CUBE (year(entreele) , cav_format) 
   
  affiche tous les totaux de toutes les combinaisons (un cube, donc !) 
   
       YEAR   CAV_FORMAT                      SUM  
      2.006   Bouteille                    278,78 
      2.006   CAISSE de 12                  20,82 
      2.006   -                            299,60 
      2.007   Bouteille                    278,15 
      2.007   CAISSE DE 6                   28,96 
      2.007   -                            307,11 
      2.008   Bouteille                      5,35 
      2.008   CAISSE de 12                  15,76 
      2.008   -                             21,11 
           -  -                            627,82 
           -  CAISSE DE 6                   28,96  <--
           -  CAISSE de 12                  36,58  <--
           -  Bouteille                    562,28  <--

 


  Enfin, vous pouvez utiliser la syntaxe suivante : 
   
  SELECT year(entreele) , month(entreele), cav_format, sum(prxactuel) 
    FROM ma_cave join vins using(vin_code) 
   GROUP BY GROUPING SETS( 
       ROLLUP(year(cav_entreele) , cav_format) , 
       ROLLUP(year(cav_entreele), month(cav_entreele)) 
                         ) 
  affiche des totaux par année/format, contenant des sous totaux par année 
   et un total général(ROLLUP), puis la même chose pour le couple année/mois 


         YEAR             MONTH   CAV_FORMAT               SUM  

      2.006                -  Bouteille              278,78 
      2.006                -  CAISSE de 12            20,82 
      2.006                -  -                      299,60 
      2.007                -  Bouteille              278,15 
      2.007                -  CAISSE DE 6             28,96 
      2.007                -  -                      307,11 
      2.008                -  Bouteille                5,35 
      2.008                -  CAISSE de 12            15,76 
      2.008                -  -                       21,11 
         -                 -  -                      627,82 
      2.006               4   -                       20,82 
      2.006               7   -                       14,40 
      2.006               9   -                      229,38 
      2.006              11   -                       35,00 
      2.006                -  -                      299,60 
      2.007               1   -                       28,96 
      2.007               2   -                       12,19 
      2.007               3   -                       20,82 
      2.007               6   -                       74,07 
      2.007               8   -                       15,59 
      2.007               9   -                      155,48 
      2.007                -  -                      307,11 
      2.008               3   -                       15,76 
      2.008               5   -                        5,35 
      2.008                -  -                       21,11 
        -                  -  -                      627,82 
   


  nouvelle fonction liée  
   
  GROUPING(col) indique si la ligne en cours est une ligne de regroupement 
   concernant cette colonne (information utile en programmation)



Exemple   

   
   SELECT year(entreele) , cav_format, sum(prxactuel) , 
          GROUPING(cav_format)  
    FROM ma_cave join vins using(vin_code) 
   GROUP BY CUBE  (year(entreele) , cav_format) 
     
 
     YEAR   CAV_FORMAT                      SUM   GROUPING  
    2.006   Bouteille                    278,78         0 
    2.006   CAISSE de 12                  20,82         0 
    2.006   -                            299,60         1  <--
    2.007   Bouteille                    278,15         0 
    2.007   CAISSE DE 6                   28,96         0 
    2.007   -                            307,11         1  <--
    2.008   Bouteille                      5,35         0 
    2.008   CAISSE de 12                  15,76         0 
    2.008   -                             21,11         1  <--
         -  -                            627,82         1  <--
         -  CAISSE DE 6                   28,96         0 
         -  CAISSE de 12                  36,58         0 
         -  Bouteille                    562,28         0

cela permet de distinguer CAV_FORMAT à NULL parce que NULL dans le fichier ou NULL parce que c'est un total

  Fonctions OLAP en 7.3


  1. (493+987)/2=740
  2. (1481+1974+10368)/3=4607
  3. (11849+11355+10861)/3=11355

 

 SOUS SELECTIONS 

   
         SQL nous donne la possibilité d'utiliser un ordre SELECT dans la clause WHERE d'un ordre SELECT. 
   
     Exemples: 
   
           LISTE DES STAGIAIRES AYANT UNE NOTE < A LA MOYENNE 
   
                SELECT * FROM STGTBL 
                WHERE note < (SELECT avg(note) FROM STGTBL) 
   
   
           même principe, précédé de la moyenne 
   
   
                SELECT NOM, NOTE FROM STGTBL 
                WHERE note <(SELECT avg(note) FROM STGTBL) 
        UNION SELECT 'MOYENNE ', avg(note) from STGTBL 


    il est alors possible (parfois obligatoire) d'utiliser les corrélations dans une sous-sélection. 
   
   
           LISTE DES STAGIAIRES AYANT UNE NOTE< A LA MOYENNE DE 
            LEUR SESSION 
   
   
           (COMPARAISON SUR LA DATE D'ENTREE) 
   
                SELECT NOM, PRENOM, NOTE FROM STGTBL S  
                          WHERE NOTE < ( SELECT avg(NOTE) 
                           from STGTBL WHERE ENTREE = S.ENTREE ) 
   
   
      il s'agit ici d'aller réactualiser la moyenne à chaque stagiaire lu 
         ( la version S du fichier. ) 
   
   
      il est possible d'imbriquer 32 sélections (attention à la lisibilité)

 



      On peut tester l'existence d'une information dans une autre table de deux manières: 
   
      LISTE DES STAGIAIRES AVEC UNE AGENCE INCONNUE 
   
               SELECT * FROM STGTBL 
                    WHERE AGENCE NOT IN (SELECT AGENCE 
                                                FROM AGETAB) 
   
             mais cela devient compliqué si la clé est complexe plus d'une colonne) 
   
   
          il faudra alors utiliser la syntaxe suivante (plus simple): 
   
   
   
               SELECT * FROM STGTBL S 
                    WHERE NOT EXISTS (SELECT * FROM 
                      AGENCE WHERE SOCIETE = S.SOCIETE and AGENCE = S.AGENCE)

  

Remarque

Il est possible d'utiliser les opérateurs ALL, ANY ou SOME 
   
      LISTE DES STAGIAIRES ENREGISTRES SOUS UNE AGENCE INCONNUE 
       SUIVIE DE LA LISTE DES STAGIAIRES AVEC LIBELLE DE L'AGENCE 
   
              SELECT S.NOM, S.PRENOM, A.LIBAGE 
                FROM STGTBL S, AGETBL A WHERE S.AGENCE = A.AGENCE 
                UNION SELECT NOM, PRENOM, 'INCONNUE ' 
                      FROM STGTBL S 
                      WHERE AGENCE <> ALL (SELECT * FROM AGETAB) 
   Mais

      
      NOT EXISTS est identique à <> ALL (SELECT ...) 
      EXISTS     est identique à  = ANY (SELECT ...) 


   
   
   EXISTS est vrai si le SELECT extrait 1 ligne ou plus 
          est faux si le SELECT n'extrait aucune ligne 
   
   
      En l'absence d'EXISTS, SOME, ALL ou ANY les sous-sélections ne doivent extraire qu'une ligne et une seule.

 

 
WITH


 V4R40 tables dérivées. peuvent éviter des vues temporaires. 
   
   On peut indiquer un ordre SELECT dans la clause from d'un ordre SELECT 
   
      ou bien déclarer juste avant le SELECT une "vue temporaire" par WITH. 
   
   
    cela permet d'éviter des créations de vues dans les cas complexes. 
   
   
  deux écritures sont donc possibles 
                                              
  WITH un_nom AS ( select ... from)            
                                                   
       SELECT ... from un_nom                              
                                             
 ou 
   
  SELECT ...   FROM   (select ... from ...) AS un_nom

 

 

 exemple , soit un fichier des cours, chaque cours est enregistré sous un module de cours. 
   
  je veux le nombre de cours du module qui en a le plus. 
 
 
 
     WITH  temp AS                                                     
    ( select count(*) as nbr from Fmodules group by codmodul )       
                                                                     
   select max(nbr)                                                   
     from temp                                                       
   
   
 
 est l'équivalent de : 
   
    create view v1 as select count(*) as nbr .... from ... 
   
 puis 
   
  select max(nbr) from v1 
   
 cela permet d'imbriquer des fonctions MAX(COUNT( ..)) étant invalide.

 

 

 

 deuxième écriture directement dans la clause FROM 
   
  soit le fichier cours (vu plus haut), 
   le fichier des modules (donnant un texte explicatif à chaque module) 
   un fichier des exemples, permettant d'attacher x exemples à un cours. 
                                                  (x pouvant être 0) 
  je veux obtenir pour chaque module 
   
   
 nom 
 texte 
 nbr de cours  
 nbr d'exemples   
 
   
   
 Nous allons donc écrire 
   
      un select qui donne le nombre de cours par module   ( C ) 
   
      un select qui donne le nombre d'exemples par module ( E ) 
   
 et réaliser une jointure entre le fichier des modules, C et E. 




 La jointure module -> C est interne (on doit avoir au moins un cours) 
   
 La jointure C -> E est externe, il peut ne pas y avoir d'exemples 
   
  select m.codmodul, texte, nbcours, nbexemples 
     from Fmodules  m 
   
        join 
   
  (select codmodul, count(*) as nbcours 
          from Fcours 
          group by codmodul) as C 
  on m.codmodul = c.codmodul 
   
   left join 
   
  (select codmodul, count(*) as nbexemples 
          from Fexemples 
          group by codmodul) as E 
  on C.codmodul = e.codmodul

 

 

 

 

                              Affichage des données  
                                               Largeur des données  . . :  79 
  Première ligne à afficher . .            Première colonne à afficher  . 
  ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+ 
  MODULE      TEXTE                                    NBCOURS      NBEXEMPLE 
   
  £CURSUSTCP  Cursus TCP/IP et INTRANET                     13            14 
  £CURSUS00   AF400 : présentation du produit                3              - 
  £CURSUS01   Initiation à l'AS/400 & OS/400                19             5 
  £CURSUS02   Base de données integrée et requête           21            13 
  £CURSUS03   Développement en RPG (RPG,PRTF,DSPF           30            28 
  £CURSUS04   Programmation système (CL et principes)       20            15 
  £CURSUS05   Télécommunications sur AS/400 .               20             6 
  £CURSUS06   Programmation COBOL/400                       28            25 
  £CURSUS220  OS/400 - V2R20                                18            42 
  £CURSUS230  OS/400 - V2R30                                32            66 
  £CURSUS3PR  Préparation à la V3R10 (études/système)       18            15 
  £CURSUS3PX  Préparation V3R10 (orienté exploitation)       9            10 
  £CURSUS310  OS/400 - V3R10                                58            47 
  £CURSUS320  OS/400 - V3R20                                19            20 
  £CURSUS370  OS/400 - V3R60 / V3R70                        24            28

                                                                                                                                                                                      A suivre...

 

 

 V5R40 : les CTE admettent maintenant un "Full select" (UNION et ORDER BY) 
   
   
    et si vous avez placé un ORDER BY dans la CTE, vous  pouvez demander à ce que ce critère de tri soit respecté dans 
     le requête finale par ORDER BY ORDER OF (le nom temporaire) 
   
    par exemple : 
   
   WITH temp AS (  select pr_code, count(*)  nbr from vins 
                      group by pr_code order by nbr) 
     Select * from temp JOIN producteurs using (PR_CODE) 
         where nbr > (select avg(nbr) from temp) 
    ORDER BY ORDER OF temp, PR_NOM 
   
    classe le résultat par nbr (comme temp) PUIS par nom. 

     
Notez :

Au passage la clause ORDER BY accepte maintenant 
 les ALIAS de colonne (en plus des positions ordinales)
 
 

 

SELECT Imbriqué

  Il est possible de placer un SELECT dans la liste des colonnes d'un Select (V5 et suivantes) 


   
  SI vous souhaitez obtenir la liste des commandes en rappelant sur chaque ligne le montant global commandé. 
   
   -> SELECT codart, (qte * prix) as montant, (select sum(qte * prix)  
                                                 from commandes) as global 
         FROM commandes 
   
  vous pouvez utiliser dans le select imbriqué, n'importe quelle variable de   n'importe quel fichier de la clause FROM du select principal,  
    si vous l'utilisez aussi dans le WHERE ou le HAVING du select imbriqué. 
   
  SI vous souhaitez obtenir la liste des commandes en rappelant sur chaque   ligne le montant global commandé pour la famille. 
   
     -> SELECT codart , qte * prix as montant, (select sum(qte * prix) 
                                                  from commandes where 
                                                    famcod = c1.famcod) as totfam 
         FROM commandes c1 
 
 

 

 

 Autre  exemple : 
 
   vous souhaitez obtenir la liste des commandes en indiquant sur chaque
     ligne le % représenté par ce montant, dans la famille :

 
      Select codart , qte * prix as montant, (select sum(qte * prix) from 
                                              commandes c2 where c2.famcod = 
                                              c1.famcod) / (c1.QTE * c1.prix) 
      from commandes c1 
   
  quelques restrictions : 
  ---------------------- 
   - Le select doit produire un résultat d'UNE ligne, d'UNE colonne 


   - Il faut attendre la V5R40 pour que GROUP BY et UNION soient valides 
      et que cette colonne puisse être utilisée par ORDER BY. 
   
  


LATERAL ou TABLE
 

 LATERAL fonctionne comme une jointure, mais en transmettant des données venant de la requête principale,
              ce qui en autorise l'utilisation dans la requête imbriquéee (p.pr_code) 

        ce n'est pas la seule manière de faire (une sous-sélection corrélée aurait marché),
         mais c'est simple et concis, particulièrement quand le select imbriqué retourne plusieurs lignes
  
Exemple


   select P.pr_nom, V.compteur from producteurs P,  
          LATERAL (select count(*) as compteur from vins 
                     where pr_code = P.pr_code ) V  

  

 

 

 Autre exemple avec ce fichier détail de commandes :
   
    N°    N°     CODE ARTICLE    QTE CDE      QTELIV   TIMECRT 
    CDE   LIGNE                                                              
      1      1    A1                 ,45         ,00   2015-10-28-07.45.14 
      1      2    A2              789,00      789,00   2015-10-28-06.45.15 
      1     45                       ,00         ,00   2015-10-26-11.45.58 
      1    555    A1               56,00       50,00   2015-10-05-05.54.28 
      2      1    A3            7.878,78    7.800,00   2015-10-28-07.45.14 
      2      2    A1            1.234,56    1.234,56   2015-10-28-06.45.15 
      2      3    A1            1.122,33      500,00   2015-10-28-05.45.16 
      3      1    A2                1,00        1,00   2015-10-28-07.45.14 
   
   
  il nous faut afficher le reste à livrer (qtecde-qteliv), quand il y en a. 
   
  en cas de doublons(plusieurs lignes ayant un reste), seule la plus ancienne 
   nous intéresse, pour cela nous utiliserons FETCH FIRST ROW ONLY. 
   
   
 
 select C1.nocde, datliv, qtecde - qteliv as restant, timecrt       
   from cdeentp1 C1, 
 LATERAL (select * from cdedetp1 where nocde = c1.nocde and qteliv < qtecde 
                                       order by timecrt 
                                 fetch first row only) as C2 
   
  Affiche 
   
     N°    DATE LIV     RESTANT   TIMECRT 
     CDE                                                       
       1   15/01/96        6,00   2015-10-05-05.54.28.824827 
       2   01/05/97      622,33   2015-10-28-05.45.16.825379 
   
   
 
 
   TABLE peut être utilisé à la place de LATERAL.



TABLE est obligatoire pour lancer un SELECT à partir d'une fonction Table (UDTF)

     SELECT * FROM TABLE(QSYS2.GROUP_USERS('FORMATION')) as G

          Donne la liste des profils utilisateurs membre du groupe FORMATION (voir SQL as a service)


 
Avec FINAL TABLE, On peut passer un ordre SELECT sur le résultat d'un INSERT 

   permettant ainsi, de retrouver facilement la valeur d'une zone IDENTITY , par exemple. 
   
  Exemple avec Clients possédant NOCLI AS IDENTITY et DATCRT de type TIMESTAMP 
   
      SELECT NOCLI, DATCRT FROM FINAL TABLE  
                 (INSERT INTO Clients (RAISOC, DATCRT) VALUES('société test', now() )) 


     NOCLI   DATCRT

               3                 2008-08-07-09.04.17.455674 

 SI vous insérez plusieurs lignes, la clause ORDER BY admet maintenant 
     INPUT SEQUENCE, demandant à ce que les lignes soit triées dans 
     l'ordre ou elles ont été insérées 
   
    Exemple 
   
   SELECT NOCLI, DATCRT FROM FINAL TABLE 
    (insert into clients (raisoc, datcrt) 
      values ('test2' , now() ) , 
             ('test3' , now() ) 
    ) 
   ORDER BY INPUT SEQUENCE 
   
   NOCLI DATCRT  
     4   2008-08-07-09.57.46.375829 
     5   2008-08-07-09.57.46.375829 
 


XMLTABLE

Fonction Table qui traite sous forme colonnée (relationnelle), les éléments d'un flux XML. La source peut être :



  SELECT X.NOM, X.RUE, X.VILLE FROM
       XMLTABLE ('$c/customerinfo' passing <source> as "c"
           COLUMNS
              NOM CHAR(30)    PATH 'name', -- élément
              RUE VARCHAR(25) PATH 'addr/street', -- élément imbriqué
              TEL VARCHAR(20) PATH '@tel' -- attribut
       ) AS X

 

Si le XML n'est pas une colonne base de données de type XML , il faut utiliser XMLPARSE( )
 
 select * from XMLTABLE(
      '$r/catalog/book' PASSING XMLPARSE(DOCUMENT GET_XML_FILE('/formation/books.xml') ) as "r"
 COLUMNS 
    ID char(5) PATH '@id',
    auteur char(10) PATH 'author',
    titre char(50) PATH 'title'
 ) as txml;

JSON_TABLE

Fonction Table qui traite sous forme colonnée (relationnelle), les éléments d'un flux JSON(même sources que XMLTABLE)

Autres Ordres

 

 L'ORDRE UPDATE 
   
              UPDATE ..........table à modifier 
               SET ............les modifs à faire 
               WHERE ..........sélection  (sans WHERE maj de 
                                           toute la table) 
   
   
 
  - Les sous sélections sont admise dans la clause SET de UPDATE
     
                                                                     
    UPDATE command C set priha = (select pritarif from article             
                                       WHERE codart = C.codart )         
         WHERE  priha = 0    
   
                                              
   
  mettre à jour le fichier command, mettre dans priha la valeur retournée 
    par la clause select qui dit : 
 
      (je vais chercher "pritarif" dans le fichier article,   de la ligne ayant le code article lu dans le fichier commande )



 

 
 UPDATE recap R set SALAIRAN = (select sum(salair + primes)              
                                 from personp1                           
                                 where matricule = R.matricule)          
   
   
 
 mettre à jour le fichier recap, mettre dans "salairAN" la valeur retournée 
   par la clause select qui dit : 
 
     (je vais chercher la somme du calcul (salair + primes)  dans le fichier du personnel des lignes ayant le matricule lu dans le fichier RECAP ) 
   
   
 l'assignation de valeurs peut se faire de deux manières: 
   
     UPDATE fichier set (col1, col2) = (select zone1, zone2 from ... where ...) 
 ou 
     UPDATE fichier set col1 = (select zone1 from fichier2 where ...) 
                       , 
                        col2 = (select zone2 from fichier3 where ...) 
 

 

 

 L'ORDRE MERGE 
   
              MERGE INTO cible C 
               USING (SELECT source1 , source2 FROM source) S 
                 ON (C.zonecle  = S.zonecle) 
                WHEN MATCHED   THEN 
                 UPDATE SET cible2 = source2 
                WHEN NOT MATCHED THEN 
                 INSERT (cible1, cible2) VALUES(source1, source2) 
 
 il s'agit de faire comme une jointure entre "cible" et "source" le but du jeu étant de faire une action sur "cible"

  Les conditions indiquées sur WHEN peuvent être complexes : 
   
        MERGE INTO cdes C 
           USING (SELECT codart , qte, prix  FROM integration) S 
            ON (C.codart = S.codart) 
         WHEN MATCHED  THEN 
              UPDATE SET qte = s.qte, prix = s.prix 
         WHEN NOT MATCHED and CODACT = 'A' THEN 
              INSERT (codart, qte, prix) VALUES(S.codart, S.qte, S.prix)
        WHEN NOT MATCHED and CODACT = 'R' THEN 
              INSERT (codart, qte, prix) VALUES(S.codart, S.qte, 0) 
         WHEN MATCHED and CODACT = 'S' THEN 
              DELETE 
 

  

 

L'ORDRE DELETE 
   
              DELETE FROM .....table à modifier 
   
               WHERE ..........sélection  (sans WHERE mise a 
                                           blanc de la table) 
   
 
 la clause WHERE supporte les sous-sélections si elles ci portent sur une autre table : 
   
   
   SUPPRIMER les clients sans commande : 
   
               Delete from client C where not exists 
                ( select * from commande where nocli = C.nocli ) 
  

 

   
  l'ORDRE TRUNCATE (7.2) 
   
        produit le même résultat qu'un CLRPFM , avec quelques options.
   
   
         TRUNCATE TABLE   CLIENTP1-----------------------------------> 
                                         |-DROP STORAGE-| 
       
   
       -------------------------------------------------------------. 
           |-IGNORE DELETE TRIGGERS-|    |-RESTART IDENTITY-| 
   
       

 


Commitment Control

 

               FONCTIONS DE VALIDATION INVALIDATION 

   -------------------------------------- 
 

  
    1/ on indique le niveau de validation avec lequel on travail 
   
          - sur la commande STRSQL (ou par F13) ou sur le paramétrage 
   
          - sur l'ordre (SELECT par exemple)  par : 
   
    SELECT (..) -------- FROM (..)-----------+--------------+------------> 
                                           |-WHERE(..)----| 
   
  >--WITH--+-NC--------------------+-----------------------------------> 
           |                       | 
           +-UR--------------------+ 
           +-CS--+---------------+-+ 
           |     |               | | 
           |     +-KEEP LOCKS----+ | 
           |                       | 
           +-RS--------------------+ 
           +-RR--------------------+ 


 
   NC      :   pas de contrôle de validation (==> validation implicite) 
 
   toutes les autres valeurs indiquent des actions base de données en attente de validation (en suspend) et indiquent les différents niveaux de disponibilité de la base de données : 
   
   UR      :   Uncomitted read 
   
               seuls les enregistrements modifiés sont verrouillés 
   
   CS      :   Cursor stability 
   
               les enregistrements modifiés plus UN enregistrement par 
                                            table en lecture 
   RS      :   read stability 
   
               les enregistrements modifiés plus TOUS les enregistrements 
                                            des tables en lecture 
   
   RR      :   repeatable read 
               toutes les TABLES sont verrouillées

 

 

  

   NONE et NO COMMIT peuvent être utilisés à la place de NC 
   
   
   CHG et READ UNCOMMITED, READ WRITE      à la place de UR 
   
   
   READ COMMITED                           à la place de CS 
   
   
   ALL                                     à la place de RS 
   
   
   SERIALIZABLE                            à la place de RR 
   
   
   les mots SERIALIZABLE, etc ... sont les termes ISO. 
   
   
   
   (les ordres UPDATE, DELETE et INSERT possèdent la même clause)

 

 


   2/ on valide (ou pas) par les ordres 

              COMMIT    Validation 
   
              ROLLBACK  Invalidation 
         
   
 
 ce sont les ordres Commit et Rollback qui définissent la frontière;  ils représentent la fin d'une transaction ET le début de la suivante

On peut changer le niveau de contrôle en cours de transaction par 
   
  SET TRANSACTION 
   
      SET TRANSACTION ISOLATION LEVEL UR 
   
  ATTENTION: lors du COMMIT (ou ROLLBACK) on retrouve la valeur par défaut 
              (celle définie par l'écran de service via F13 ou par la config. ODBC/JDBC)

 


 

 La version 6 avait apporté à l'ordre SELECT l'option SKIP LOCKED DATA  
  cela permettait lors d'une lecture avec verrouillage d'enregistrement (*CS ou plus), d'ignorer les lignes verrouillées

    Cette option a été étendue aux instructions UPDATE et DELETE en V7.  


Vous avez, en plus, sur cette version (7), le choix entre trois comportements : 
   
   WAIT FOR OUTCOME 
     Attendre que les lignes soient libérées (COMMIT par exemple) 
      cela n'a pas d'effet sur les niveaux de COMMIT inférieurs à CS 
   
   USE CURRENTLY COMMITTED 
     Utiliser les valeurs déjà validées 
      cela ne peut s'appliquer qu'aux COMMIT niveau CS (sans KEEP LOCKS) 
   
   SKIP LOCKED DATA 
    les lignes verrouillées sont ignorées. Comme cette clause est maintenant 
     valide avec UPDATE/DELETE, elle peut être utilisée avec tous les niveaux 
     de COMMIT, sauf RR (repeatable read)

 

 

 

 

Requêtes récursives

 

 Requêtes récursives  (V5R40)
  -------------------- -------
   
  Soit le fichier suivant (vision "éclatée" d'un vélo) : 
   
     CPST        CPSE        LIBELLE      
     01 cadre    00 vélo     le cadre 
     02 fourche  01 cadre    pour les roues 
     03 frein    02 fourche  X 2 
     04 chaine   00 vélo     à graisser ! 
     05 pédales  00 vélo     X 2 
     06 patin    03 frein    à surveiller 
     07 roue     00 vélo     X 2 
     08 plateau  00 vélo     peut-être plusieurs 
     09 selle    00 vélo     obligatoire 
     10 pignon   00 vélo     à graisser 
     11 tige     09 selle    pour la selle 
     12 pneu     07 roue     à gonfler 
     13 tube     01 cadre    léger si possible 
     14 chambre  12 pneu     voir rustines 
     15 valve    14 chambre  pour gonfler



Nous voulons afficher le composant 1 (le cadre) et toutes les lignes dépendantes: 
   
   Il faut, dans une clause WITH, afficher la ligne concernée, puis placer 
    en dessous avec UNION ALL, le résultat de la jointure entre elle même  
    et le fichier des liens. 
   
   WITH temp (NIVEAU, CPST, CPSE, LIBELLE) 
     as (select 1 , CPST, CPSE, libelle 
         from TBliens where libelle = 'le cadre' 
   UNION ALL 
       select P.niveau+1 , F.CPST, P.CPST , f.libelle 
           from temp P join TBliens F on  P.CPST=F.CPSE 
      ) 
   SELECT * FROM TEMP 
   
 Affiche : 


   NIVEAU   CPST        CPSE        LIBELLE     
        1    01 cadre    00 vélo    le cadre 
        2    02 fourche  01 cadre   pour les roues 
        2    13 tube     01 cadre   léger si possible 
        3    03 frein    02 fourche X 2 
        4    06 patin    03 frein   X 2 
   
              

  

   WITH temp (NIVEAU, CPST, CPSE, LIBELLE) 
     as ( select 1 , CPST, CPSE, libelle from TBliens where CPSE = '00 vélo' 
       UNION ALL 
        select P.niveau+1 , F.CPST, P.CPST , f.libelle 
           from temp P join TBliens F on  P.CPST=F.CPSE ) 
   SELECT * FROM TEMP  

 affiche l'arborescence complète :  


   NIVEAU   CPST        CPSE        LIBELLE     
       1   01 cadre    00 vélo     le cadre 
       1   04 chaine   00 vélo     à graisser ! 
       1   05 pédales  00 vélo     X 2 
       1   07 roue     00 vélo     X 2 
       1   08 plateau  00 vélo     peut-être plusieurs 
       1   09 selle    00 vélo     obligatoire 
       1   10 pignon   00 vélo     à graisser 
       2   02 fourche  01 cadre    pour les roues 
       2   13 tube     01 cadre    léger si possible 
       2   12 pneu     07 roue     à gonfler 
       2   11 tige     09 selle    pour la selle 
       3   03 frein    02 fourche  X 2 
       3   14 chambre  12 pneu     voir rustines 
       4   06 patin    03 frein    X 2 
       4   15 valve    14 chambre  pour gonfler

 

 

 

 

Vous remarquerez que SQL traite les liens dans l'ordre ou ils apparaissent, c'est a dire dans l'ordre des niveaux

 Si vous souhaitez faire apparaitre toute la profondeur d'une branche (tous les CPST de '01') avant de passer à la branche suivante, ajoutez : 
   
   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 (NIVEAU, CPST, CPSE, LIBELLE) 
     as (select 1 , CPST, CPSE, libelle from TBliens where CPSE = '00 vélo' 
   UNION ALL 
       select P.niveau+1 , F.CPST, P.CPST , f.libelle 
           from temp P join TBliens F on  P.CPST=F.CPSE ) 
    SEARCH DEPTH FIRST BY CPST SET tri  
 

 
affiche

   NIVEAU   CPST        CPSE        LIBELLE      
        1    01 cadre    00 vélo    le cadre 
        2    02 fourche  01 cadre   pour les roues 
        3    03 frein    02 fourche X 2 
        4    06 patin    03 frein   X 2 
        2    13 tube     01 cadre   léger si possible 
        1    04 chaine   00 vélo    à graisser ! 
        1    05 pédales  00 vélo    X 2 
        1    07 roue     00 vélo    X 2 
        2    12 pneu     07 roue    à gonfler 
        3    14 chambre  12 pneu    voir rustines 
        4    15 valve    14 chambre pour gonfler 
        1    08 plateau  00 vélo    peut-être plusieurs 
        1    09 selle    00 vélo    obligatoire 
        2    11 tige     09 selle   pour la selle 
        1    10 pignon   00 vélo    à graisser 
   
 
 Enfin, la clause CYCLE , évite les boucles infinies  (si les données le permettaient) 

 Imaginons une erreur de saisie dans notre fichier 
   
    il a été ajoutée la ligne suivante 
     '06 patin' est composé de '00 vélo' (un comble !) 


      ==> AIE, AIE, on boucle. 
   
   INSERT INTO TBLIENS values('00 vélo', '06 patin', 'on boucle') 
   
    --> sur une session 5250, il faut interrompre par appel système/2 !
 pour éviter cela : 
   
  CYCLE (nom de colonne) SET vartemp = flag1  DEFAULT valeur0 
 
 quand SQL va se rendre compte qu'il boucle (une ligne déja vue)
  il va attribuer à VARTEMP (nouvelle variable interne à la requête), la valeur "flag1"(dans les autres cas, elle contient valeur0)

  la ligne va quand même être affichée, mais la boucle se termine

  

  Exemple avec : 


   WITH temp (NIVEAU, CPST, CPSE, LIBELLE) 
   as ( toujours le même select ... 
        ) 
    SEARCH DEPTH first by CPST SET tri 
    CYCLE CPST SET flag to '1' DEFAULT '0'  
   Select FLAG, niveau , CPST, CPSE, libelle FROM temp 
    ORDER BY tri

 résultat
 
   
    FLAG      NIVEAU   CPST        CPSE        LIBELLE     
     0             1    01 cadre    00 vélo    le cadre 
     0             2    02 fourche  01 cadre   pour les roues 
     0             3    03 frein    02 fourche X 2 
     0             4    06 patin    03 frein   X 2 
     0             5    00 vélo     06 patin   on boucle 
     1             6    01 cadre    00 vélo    le cadre     <== déja vu ! 
     0             6    04 chaine   00 vélo    à graisser ! 
     0             6    05 pédales  00 vélo    X 2 
     0             6    07 roue     00 vélo    X 2 
 
  A vous donc de rajouter la CLAUSE CYCLE si vous craignez que les données  puissent engendrer des boucles sans fin.

   

 

Requêtes récursives en V7R1 

 
 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 avec la syntaxe V5R40 : 
   
  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, en V7R1,  obtenir 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 (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 
   
  
   
 
 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é. 
 

 

 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 
   
  avec UNION 
   
     FLIGHTS contient les vols d'avions, TRAINS les trajets en train 
             et vous voulez déplacer sans tenir compte 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'

 

 

•on peut faire une requête hiérarchique basée sur plusieurs tables 
   
  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 
    



 En résumé, une requête hiérarchique utilise cette syntaxe (V7) :  

  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 l'extrémité d'une branche