ORGANISATION DE L'INFORMATION SUR UN AS/400.








     L'ensemble espace de stockage (les disques magnétiques)
             et espace de travail  (la mémoire centrale)
     est considéré dans l'AS/400 comme un tout, qui est appelé
     Espace Adressable Unique.






     Le système doit donc mettre en œuvre une organisation lui
     permettant de retrouver chaque information :
             c'est la notion d'OBJET.

 L'OBJET 


   Définition : est appelé OBJET tout élément identifié, contenant
                sa propre description, et manipulable par les commandes
                du système.


                ex: les PROGRAMMES, les FICHIERS




   Identification : un OBJET est identifié par 3 éléments, dont la
                    combinaison sera UNIQUE dans le système


                    - son NOM donné par le créateur de l'OBJET


                    - son TYPE ex : *FILE (pour les FICHIERS base de données)
                                    *PGM  (pour les PROGRAMMES)
                                  
                                     etc...


                    - sa  BIBLIOTHÈQUE  de rattachement

 LA BIBLIOTHÈQUE 




    Une BIBLIOTHÈQUE est un OBJET dont le contenu est une LISTE D'OBJETS.


    On pourrait comparer une BIBLIOTHÈQUE au Répertoire des ouvrages
    d'une bibliothèque de livres. Celui-ci répertorie tous les
    ouvrages de la bibliothèque et indique l'endroit PHYSIQUE où
    ils se trouvent.




    TOUT OBJET EST RATTACHE A UNE BIBLIOTHÈQUE ET UNE SEULE. 



L'administrateur met à votre disposition une liste des bibliothèques avec lesquelles
vous travaillez régulièrement  : *LIBL

 LE FICHIER 


   Les données sont stockées dans des Objets de type FICHIER (*FILE).




   Dans l'AS/400 nous trouverons 2 types de Fichiers :




         - le Fichier PHYSIQUE (aussi appelé TABLE)


         - le Fichier LOGIQUE  (aussi appelé VUE)




    

                            FICHIER PHYSIQUE 




     C'EST LE RÉSERVOIR DE DONNÉES 




         - ATTRIBUTS  ----> Caractéristiques du Fichier, dont
                            le FORMAT des enregistrements


                            (plusieurs Formats peuvent être utilisés,
                             mais ce n'est pas le cas général)




         - DONNÉES    ----> Sont stockées dans des MEMBRES


                            (plusieurs Membres peuvent être utilisés, mais
                             un seul est obligatoire. En général il n'en
                             existe qu'un, du même nom que le Fichier)

   Les données sont placées dans des ENREGISTREMENTS.
         par ex. :
         le Fichier Client contiendra autant d'enregistrements
         que de Clients répertoriés.




   Les Enregistrements sont composés de ZONES.
         par ex. :
         le Fichier Client contiendra, dans chaque Enregistrement, un
         ensemble de Zones, dont au minimum :
         un Code Client, un Nom, une Adresse, des Conditions de Règlement,
         une Date de création, etc...




   Dans la partie ATTRIBUTS de l'Objet Fichier, on trouvera le nom
   d'un FORMAT contenant la description des ZONES.

  Exemple de Fichier :
                                   ZONES (ou Champs ou Colonnes)
                                     |
                      _______________v___________________
                      |        |        |        |      |
 ENREGISTREMENTS      |        |        |        |      |
  (ou Lignes)     ....v........v........v........v......v.....
     |            : CODE :  NOM      : AGENCE :CATEG: DATE   :
     |            :......:...........:........:.....:........:
     |    |-----> : 101  : DUBOIS    :   01   : 07  : 930405 :
     |    |-----> : 107  : ERNST     :   01   : 12  : 891215 :
     |    |-----> : 110  : DUPONT    :   02   : 14  : 890405 :
     |    |-----> : 102  : MERCIER   :   03   : 18  : 910302 :
     +--->|-----> : 104  : BOZUFFI   :   03   : 12  : 900302 :
          |-----> : 121  : GARDEL    :   01   : 17  : 921215 :
          |-----> : 130  : FLAVARD   :   02   : 09  : 920405 :
          |-----> : 132  : GOUDE     :   02   : 13  : 890405 :
          |-----> : 103  : FORTRAN   :   03   : 17  : 900302 :
          |-----> : 105  : DUBROVNIK :   01   : 16  : 931215 :
                  :..........................................:

  NORMALISATION DES FICHIERS
 ---------------------------


  Il faut, tout d'abord effectuer un RECENSEMENT DES DONNEES




  Ce travail a pour but d'aboutir à la rédaction d'un "dictionnaire"
  où les données seront identifiées par :


                                 + un  NOM
                                 + une DÉFINITION (en clair)
                                 + une STRUCTURE  (alpha,num..)
                                 + un  TYPE (calcul ,saisie,...)
                                 + une QUANTIFICATION
                                           (nb de valeurs possibles)
                                 + la liste des valeurs admises



    puis  LA SYNTHÈSE DES DONNÉES  afin d'établir un dictionnaire définitif


    

   A ce stade il doit être possible de distinguer une clé permettant
     d'identifier un élément (enregistrement)


   CLÉ = ATTRIBUT ou ENSEMBLE PERMETTANT D'IDENTIFIER TOUS LES AUTRES




     Clé PRIMAIRE  =  identifiant UNIQUE d'une ligne dans un fichier.






  LES FORMES NORMALES (proposées par le modèle de "CODD") :


      Celles ci nous imposent 


             ==> PAS D'OCCURRENCE.
                 PAS DE REGROUPEMENT DE DONNÉES.

             QUE TOUS LES ATTRIBUT DÉPENDENT DE LA CLÉ,
                                   DE TOUTE LE CLÉ,UNIQUEMENT DE LA CLÉ


       ceci nous amène parfois à multiplier les fichiers

Exemple:
--------

.impossible de placer les commandes dans le fichier client :

 on ne peut pas prévoir le nombre de commandes, donc impossible de faire 

.impossible de dupliquer les informations client

 on ne peut pas faire :
  (trop de risques d'erreurs si le client change de nom) 

nous sommes donc obligé de faire un fichier client,

puis un fichier commande dans lequel le n° de client (et uniquement le n°)
nous permet d'établir un lien logique avec le fichier client

pour les mêmes raisons , le fichier commandes sera éclaté en deux

                            FICHIER LOGIQUE 






    Un Fichier Logique est un Objet de type *FILE.


    Il possède une Description et (au moins) un FORMAT.
    Il comprend (au moins) un MEMBRE.



    MAIS CE MEMBRE NE CONTIENT PAS DE DONNÉES.




    Il ne possède qu'un chemin d'accès permettant de retrouver les
    données d'un ou de plusieurs Fichiers Physiques.

 LE FICHIER LOGIQUE VA NOUS PERMETTRE :


    - d'accéder dans un nouvel ordre aux Enregistrements du Fichier Physique
      (ex. par Département dans le fichier Client, au lieu du Numéro Client)


    - d'utiliser une autre description des Enregistrements, et notamment
      de ne pas accéder à certaines Zones
      (ex. ne pas voir la zone Salaire, ou Adresse, du fichier du Personnel)


    - de regrouper les données de plusieurs Fichiers Physiques
      (ex. voir dans un seul ensemble de données: les Commandes Clients, les
       informations Clients et le détail des Articles commandés)


    - de sélectionner une partie des Enregistrements d'1 ou de plusieurs
      Fichiers Physiques, et donc de réduire le volume des données traitées
      (ex. n'accéder qu'aux Clients de certaines Agences)

  EXEMPLE:     

    Les outils de Requête vont vous permettre de faire
   ------------------------------------------------


      SELECTION    Restriction de lignes


      PROJECTION   Restriction/réorganisation des colonnes


      UNION        Regroupement de deux relations (l'une après l'autre)


      JONCTION     Restriction sur le produit cartésien de X relations






      SELECTION (clients de l'agence 01)
      ..................................................
      : NUMCLI :   NOM            :  PRENOM   : AGENCE :
      :........:..................:...........:........:
      :  101   :   DUBOIS         :  Eric     :   01   :
      :  107   :   ERNST          :  Patrick  :   01   :
      :  121   :   GARDEL         :  Sophie   :   01   :
      :........:..................:...........:........:

      PROJECTION (nom,agence)
      .............................
      :   NOM            : AGENCE :
      :...........................:
      :   DUBOIS         :   01   :
      :   ERNST          :   01   :
      :   DUPONT         :   02   :
      :   MERCIER        :   03   :
      :   BOZUFFI        :   03   :
      :   GARDEL         :   01   :
      :   FLAVARD        :   02   :
      :   GOUDE          :   02   :
      :   FORTRAN        :   03   :
      :   DUBROVNIK      :   02   :
      :..................:........:


JONCTION (sur agence)




      .........................................................
      : NUMCLI : NOM        : PRENOM   : AGENCE : LIBAGE      :
      :........:............:..........:........:.............:
      :  101   : DUBOIS     : Eric     :   01   : PARIS       :
      :  107   : ERNST      : Patrick  :   01   : PARIS       :
      :  110   : DUPONT     : Alain    :   02   : NANTES      :
      :  102   : MERCIER    : Anne     :   03   : LYON        :
      :  104   : BOZUFFI    : Ricardo  :   03   : LYON        :
      :  121   : GARDEL     : Sophie   :   01   : PARIS       :
      :  130   : FLAVARD    : Cecile   :   02   : NANTES      :
      :  132   : GOUDE      : Jean     :   02   : NANTES      :
      :  103   : FORTRAN    : Yves     :   03   : LYON        :
      :  105   : DUBROVNIK  : Marie    :   01   : PARIS       :
      :........:............:..........:........:.............:

         STRSQL
                

                    NAMING(*SYS)     (convention d'appellation)


                     *SYS  convention d'appellation système
                           (LIB/OBJET)
                           un objet non qualifié est recherché dans  *LIBL  
                             (ADDLIBLE xxx, pour ajouter) 


                     *SQL  convention d'appellation SQL
                           (LIB.OBJET)
                           un objet non qualifie est recherché dans une
                           bibliothèque ayant le nom du USER


  

                    LIBOPT(*LIBL)    (liste de bibliothèque)


                     Liste de bibliothèque utilisee par F16-F17-F18

                     *LIBL    liste de bibliothèques du travail
                     
                     -NOMBIB- une bibliothèque particulière


                    REFRESH(*ALWAYS) (mise à jour de l'écran)

                     Quand réactualiser les données provenant d'un SELECT


                     *ALLWAYS  à chaque fois que l'utilisateur
                               demande un défilement.
                     *FORWARD  uniquement à la première visualisation
                               d'un écran (défilement avant)


                    DATFMT( )
                    DATSEP( )  définissent la présentation des colonnes
                    TIMFMT( )   de type DATE/HEURE.
                    TIMSEP( )

 Touches de commandes valides sur l'écran SQL


             F3  = EXIT


             F4  = INVITE


             F6  = INSERER UNE LIGNE
                   une ligne blanche est insérée en-dessous
                   de la ligne ou se trouve le curseur


             F9  = RAPPEL d'une instruction SQL
                   (ou sous-selection sur INVITE d'un select)


             F10 = COPIER UNE LIGNE
                   la ligne où se trouve le curseur est copiée
                   en-dessous


             F13 = SERVICE
                   affiche l'écran de service permettant de
                   modifier les paramètres de la commande STRSQL
                   et de choisir le type de sortie.
                      (ECRAN,FICHIER D'IMPRESSION,FICHIER BD)

             F14 = SUPPRIMER UNE LIGNE
                   supprime la ligne où se trouve le curseur


             F15 = SCINDER UNE LIGNE
                   place tout ce qui se trouve à droite du
                   curseur sur une ligne en-dessous




             F16 = LISTE DE BIBLIOTHEQUES


             F17 = LISTE DE FICHIERS D'UNE BIBLIOTHEQUE


             F18 = LISTE DES ZONES D'UN FICHIER




             F24 = AUTRES TOUCHES




  F6, F10, F14 et F15 sont valides aussi sur l'écran d'invite.

    SQL/400 langage relationnel de manipulation de données.
   ----------------------------------------------------------


         ORDRES SQL EXECUTABLES SUR L'ECRAN SQL-INTERACTIF




      l'ORDRE SELECT  (définition d'une extraction)


             SELECT ..........ce que je veux


              FROM ...........à partir de quel(s) fichier(s)


              WHERE ..........sélection


              GROUP BY .......traitement récapitulatif


              HAVING .........sélection sur trt récap.


              ORDER BY .......ordre de classement

   SELECT
   -------




                 *   toutes les variables du(des) table(s)


                        [ex : select * from articles]




             ou




                 <expression1> [AS nom],


                  [  <expression2> [AS nom], .... ]








            TOUTE VIRGULE DOIT ETRE SUIVIE D'UN ESPACE (EN FRANCE)

    toute expression peut se voir attribuer dynamiquement un nouveau nom
      qui lui servira aussi d'en-tête.




    - SELECT NOM, SALAIRE + COM AS TOTPAIE FROM PERSON


      attribue le "nom" TOTPAIE à l'expression  SALAIRE + COM




             expressions valides: 


             + un nom de variable


             + une constante


             + un calcul


                          + ,  *  ,  -  ,  /


               ex : (QTE * PU) , (SALBRU * 13) etc ...

             + une fonction


                        sous la forme Fonction(argument) 




               toutes les fonctions acceptent comme argument une
                expression (au sens qui vient d'être vu).


               les fonctions peuvent donc être imbriquées.




 TRANSLATE(raisoc) transforme la raison sociale en majuscule


 SUBSTR(RAISOC , 2 , 5) extrait les caractères 2,3,4,5 et 6




 TRANSLATE( SUBSTR(RAISOC , 2 , 5) )
  transforme en majuscules les caractères extraits


  etc...

 Exemples :


           +  SELECT CODART, LIBART, QTESTO, PUART,
                     (QTESTO * PUART) as montant FROM SQLDB/STOCKP1


           +  SELECT MATRIC, NOM, SUBSTR(PRENOM, 1, 8),
                      (SALBRU * 13) AS SALAIRE
                     FROM SQLDB/PERSONP1


           + SELECT NOM, (CURRENT DATE - ARRIVEE)
                     FROM SQLDB/PERSONP1


           + SELECT NOM, PRENOM, ADRESSE , VILLE
                     FROM SQLDB/PERSONP1


           + SELECT TRANSLATE(ADRESSE) ...

 remarque :


               La qualification BIBLI/TABLE n'est valide
                  que si la convention d'appellation système
                  a été choisie sur la commande STRSQL.


               Si la table n'est pas qualifiée la recherche
                  est faite dans *LIBL






               Il faudra qualifier bibli.table si on choisit
                  la convention d'appellation SQL.


               Si la table n'est pas qualifiée la recherche
                  est faite dans la bibliothèque ayant le même
                  nom que l'utilisateur (owner)

   FROM
  -------
               TABLE1, TABLE2,..      (32 tables maxi)






             IL EST POSSIBLE DE QUALIFIER LA TABLE
                       (suivant la convention choisie)


             IL EST POSSIBLE D'ASSOCIER A UNE TABLE
                    UNE CORRELATION  (un nom interne)
                    QUI POURRA ETRE UTILISEE EN DEFINITION
                    DE COLONNE




   exemple:
                   FROM SQLDB/STGTBL A, SQLDB/AGETBL B


                   La table STGTBL est connue sous le "nom" A
                            AGETBL sous le "nom" B

   WHERE
  -------




             SELECTIONS POSSIBLES




            +                    =                 egal
                                <>                 non egal
                 (expression1>   >  <expression2>  sup
                                 <                 inf
                                >=                 sup ou egal
                                <=                 inf ou egal






             WHERE codart = 0 ....




             WHERE  qtecde <> 0 ...

             +   <expression1>  BETWEEN <exp2> AND <exp3>
                    compris entre <exp2> et <exp3> bornes
                                                   incluses




                  WHERE DEPT BETWEEN 22 and 44




                                       I-----USER----------I
             +  <expression1>  LIKE    I--CURRENT SERVER---I
                                       I--'chaine de car'--I
                                       

               Un "%" dans la chaine de caractères indique
                  que n'importe quel nombre de n'importe
                  quel caractère peut occuper cette position


               Un "_" (soulignement) dans la chaine de
                  caractère indique que n'importe quel
                  caractère peut occuper cette position.

                  ex: LIKE '%ABC%'
                       est vrai si la chaine "ABC" est trouvée
                        à n'importe quel endroit de la variable
                  ex: LIKE 'ABC%'
                       est vrai si la variable commence par "ABC"
                  ex: LIKE 'A_BC%'
                       est vrai si la variable commence par "A"
                        suivi d'un caractère quelconque  puis
                        par les deux lettres "BC"




               USER   le contenu de la variable est comparé
                      avec le profil utilisateur en cours
                      (si < ou = à 8 caracteres)


               CURRENT SERVER le contenu de la variable est comparé
                      avec le nom du serveur en cours (ma machine)
                      vu de type VARCHAR(18).

             +   <expression1> IN (<exp2>, <exp3>)
                      Liste de valeurs à comparer


                             ces valeurs pouvant être:


                             I-USER----------I
                       ------I-'constante'---I    (cf LIKE)
                             

                 WHERE DEPT IN (22, 29, 35, 44)


          .......................................................
          : Remarque :                                          :
          : Il est toujours préferable de comparer une colonne  :
          : avec une valeur de même définition, même longueur.  :
          :.....................................................:




           TOUTES CES CONDITIONS PEUVENT être RELIEES PAR
           DES "OR" ET DES "AND" et/ou inversées (niées) par "NOT".

           EXEMPLES DE SELECTIONS VALIDES 




           +  SELECT * FROM SQLDB/STGTBL WHERE AGENCE = 01


           +  SELECT NUMSTG, NOM, PRENOM FROM SQLDB/STGTBL
                     WHERE NUMSTG BETWEEN 0001 AND 0010
                     AND AGENCE <> 01


           +  SELECT NOM, AGENCE FROM SQLDB/STGTBL
                     WHERE NOM LIKE "D%"




           on peut bien sur utiliser des fonctions




           +  SELECT NOM, AGENCE FROM SQLDB/STGTBL
                     WHERE TRANSLATE(NOM) LIKE "D%"

 fonctions valides: 


 fonctions de changement de genre


 DIGITS(exp) 
                                 représentation en alpha
                                 d'une expression numérique, utile pour SUBSTR


                                 DIGITS(ZONDEC)


 DECIMAL(exp, l, d) 
                    représentation en packé d'une expression numérique


                                 DECIMAL(AVG(SALBRU), 7, 2)


  forcer une précision :         DECIMAL((QTE * PU), 11, 3)


 

 INTEGER(exp)       représent. en entiers
                               d'une expression décimale





 CHAR(date) renvoi la représentation caractère d'une date.
             (séparateurs inclus, suivant le format en cours.)






 la nouvelle fonction CAST vient remplacer toutes les fonctions
   précédentes en proposant d'indiquer clairement le nouveau type.




 (attention CAST remplace les zéros de gauche par un espace, pas DIGITS)

 CAST(expression AS type[lg]) 


  types valides    INT(INTEGER)--|
                   SMALLINT-----------------------------------------------


                   DEC(DECIMAL)---|
                   NUMERIC---------(longueur, nb-de-décimales)--------------


                   FLOAT---|
                   REAL    -------------------------------------------------
                   DOUBLE--|


                   CHAR(CHARACTERE)-|             |--FOR BIT DATA--|
                   VARCHAR------------(lg)---FOR-- --FOR SBCS --------------
                                                  |---n°-ccsid *---|
                   DATE----------|
                   TIME-----------------------------------------------------
                   TIMESTAMP-----|


* : un CSSID est un code-page associé à la donnée, indiquant la langue (france = 297)

 Autres fonctions valides: 


 CONCAT( )         ancienne syntaxe   :  nom CONCAT prenom [ou !!]
                      maintenant en plus : CONCAT(nom, prenom).






 SUBSTR(exp, dep, lg) 
                    extrait une chaîne de caracères depuis "dep" sur
                                  'lg' caractères à partir d'une
                                  expression alpha.




 Autre écriture  : SUBSTRING(zone FROM début ------------------------)
                                                 !--FOR longueur--!


    si longueur n'est pas indiqué on va jusqu'à fin de zone


 LEFT(zone, lg) 
                    Extrait les "lg" caractères de gauche.

 RIGHT(zone, lg) 
                    Extrait les "lg" caractères de droite.

 LENGTH(expr)       donne la longueur physique d'une expression.
                     (le résultat est donné sous forme binaire)


                    ex :SUBSTR(nom, LENGTH(nom) -1 , 2)




 CHARACTER_LENGTH   = nbr de caractères 




                    ex :CHARACTER_LENGTH(trim(nom))


 ABSVAL(exp)        renvoie la valeur absolue d'une expression
                                  numérique


 SQRT(exp)          renvoie la racine carrée


 MOD(exp1 exp2)     renvoie le reste de la division des deux arguments


 POWER(nombre, exposant)   élévation à la puissance

   SIGN(colonne)     retourne le signe d'une colonne
                      -1 si négatif, 1 si positif strictement, 0 si null


   CEIL(colonne)     Transforme un réel (avec décimales) en son entier
                      immédiatement supérieur.


                      CEIL(2,42) = 3        CEIL(2,56) = 3

   FLOOR(colonne)     Transforme un réel (avec décimales) en son entier
                      immédiatement inférieur.


                      FLOOR(2,42) = 2        FLOOR(2,56) = 2


   ROUND(colonne,p)    arrondi comptable d'une expression numérique
                        avec choix de la precision.


                     ROUND(2,42 , 1) = 2,40     ROUND(2,56 , 1) = 2,60


   TRUNCATE(colonne,p) rétrait de décimales d'une expression numérique
                        avec choix de la précision.




                     TRUNCATE(2,42 , 1) = 2,4    TRUNCATE(2,56 , 1) = 2,5

 RRN(nom-table)    renvoie le numéro de rang.


 TRANSLATE(exp)    conversion minuscule/MAJUSCULE d'une chaîne
 ou UCASE( ) ou UPPER( ) 


 TRANSLATE(chaîne----------------------------------------------------->
                 !                                             !
                 !---, remplacement ---------------------------!
                                   !                  !
                                   !-- , origine -----!


          >-------------------------------)
               !                   !
               !--PADDED WITH------!




     Exemple: TRANSLATE(var1 ,' ,F' , '0.$')
              remplace  0 par ' '  "." par "," et "$" par "F" .


 LOWER( ) ou LCASE( ) V4R40
                   conversion MAJUSCULE/minuscule d'une chaîne

 LTRIM( ) , RTRIM( ) et TRIM( ) 


    Supprimme les espaces à gauche, à droite ou aux deux extrémités.


 STRIP(exp, [BOTH]      , [C]) 
             LEADING
             TRAILLING


                         supprime le(s) caractère(s) "C" (dft = blanc)
                          à gauche (LEADING), à droite (TRAILING) ou les
                          deux (BOTH), de la chaîne indiquée.
 


 VALUE(exp1, exp2, exp...) 
 COALESCE et IFNULL  alias de VALUE()


   renvoient la première valeur non nulle de la liste des expressions


   Intéressant sous la forme IFNULL(NOM, 'inconnu')
     pour attribuer une valeur par défaut ('inconnu') si NULL.

 LOCATE(recherche, origine, ---------------) V4R20
                            |-départ--|


  indique si "recherche" est présent dans "origine" (à partir de "départ").
   l'information retournée est numérique et indique la position de début.


 POSITION(recherche IN origine) [alias à LOCATE]


 NULLIF(argument1, argument2) 


  retourne NULL, si les deux arguments sont égaux.


 
 CHAR( )        transforme à taille fixe une variable à lg variable.
 VARCHAR( )     transforme à lg variable une variable à taille fixe.

 + 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 la 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 ...                            :
       :......................................................:


  .........................................................................
  :  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

   GROUP BY
  ----------


               Cette clause ne donne qu'une ligne par groupe
                d'enregistrements


               il n'est pas possible de demander des variables
               qui ne sont pas precisées dans le GROUP BY


   Fonctions associées : 


   ces fonctions utilisées sans GROUP BY donnent un résultat général




              AVG(exp)    moyenne


                           ex. AVG(SALBRU) moyenne des salaires


              COUNT(*)    nb d'enregistrements sélectionnés

              COUNT(DISTINCT col1) nb de valeurs rencontrées pour col1.


          ex: SELECT COUNT(DISTINCT nocli) FROM ENTETECDE
                     WHERE DATLIV > CURRENT DATE


              ici, il faut compter le nombre de clients et non le nombre
                   de lignes qui donnerait le nombre de commandes.




              MAX(exp)    valeur la plus grande rencontrée (y compris dates)


                           ex: MAX(SALBRU) plus grand salaire


                 mais aussi : MAX(PRXACHAT PRXMOYEN)
                                la plus grande des deux colonnes




              MIN(exp)    valeur la plus petite rencontrée (y compris dates)


                           ex: MIN(SALBRU) plus petit salaire


                 mais aussi : MIN(DAT1 DAT2), idem fonction MAX.

              SUM(exp)    somme


                           ex: SUM(SALBRU) somme des salaires


                               SUM(QTESTO * PUART)  somme des montants




              VAR(exp)    variance


                           la formule est VAR(x) = SUM(x**2)/COUNT(x) - (
                                                     (SUM(x)/COUNT(x))**2)


                            pour [10,12,7] la variance est 4,22


              STDDEV(exp) écart-type


                           la formule est STDDEV(x) = SQRT(VAR(X))


                            pour [10,12,7] l'écart-type est 2,04

           EXEMPLES DE SELECTIONS VALIDES




           +  SELECT COUNT(*), AGENCE FROM SQLDB/STGTBL
              GROUP BY AGENCE
               donne le nombre de stagiaires par agence




           +  SELECT AVG(SALBRU), SERVICE, COEFF
              FROM SQLDB/PERSONP1 GROUP BY SERVICE, COEFF
               donne la moyenne des salaires par service
                et coef du fichier personnel






           EXEMPLES DE SELECTION INVALIDE 


           +  SELECT COUNT(*), NOM, AGENCE FROM SQLDB/STGTBL
              GROUP BY AGENCE
               la variable nom n'identifiant pas un groupe

   HAVING
  --------


               Permet de donner des conditions sur le résultat
                d'un traitement récapitulatif




           EXEMPLE




           +  SELECT COUNT(*), AGENCE FROM SQLDB/STGTBL
              GROUP BY AGENCE HAVING COUNT(*) > 20


               donne le nombre de stagiaires par agence, uniquement
                 pour les agences ayant plus de 20 stagiaires

   ORDER BY
  ----------


               Permet de donner un ordre de classement


               ORDER BY ----nom-colonne------------------------.
                         I--n° de déclaration-I  I-ASC--I
                                                 I-DESC-I


         Sur le résultat d'un GROUP BY les enregistrements sont
          classés sur le critère de traitement récapitulatif


           EXEMPLES DE SELECTIONS VALIDES




           +  SELECT * FROM SQLDB/STGTBL ORDER BY NOM


           +  SELECT MATRIC, NOM, SUBSTR(PRENOM, 1, 8)
                     FROM SQLDB/PERSONP1 ORDER BY 3
                               (classement sur début du prénom)

 les expressions sont maintenant admises sur les clauses


     GROUP BY    et   ORDER BY


 à condition qu'elles soient indiquées aussi (et à l'identique) sur la
    clause SELECT.


 soit bdist dans le fichier clients contenant dep+bureau (44000 par ex.)




 SELECT substr(digits(bdist), 1, 2), COUNT(distinct NOCLI)


         from clients


        group by substr(digits(bdist), 1, 2)




 donne le nombre de clients par département.

 SELECT bdist, COUNT(distinct NOCLI)


         from clients


        group by substr(digits(bdist), 1, 2)


est invalide, l'expression n'étant pas présente sur le SELECT.




ORDER BY offre rigoureusement les mêmes possibilités.


 tout en conservant la possibilité d'indiquer un N° de colonne
      (N° dans le select bien sur)


                                 Ou
                                 |
 SELECT codcli, left(raisoc, 20) |  SELECT codcli, left(raisoc, 20)
         from clients            |          from clients
        ORDER BY 2               |         ORDER BY left(raisoc, 20)
                                 |
                                 |

V6R10

 Soit un fichier CAVE et le  GROUP BY suivant :


  SELECT annee , cav_format, sum(prxactuel)
    FROM ma_cave join vins using(vin_code)
   GROUP BY annee , cav_format


 affichant


     ANNEE   CAV_FORMAT                      SUM 
     2.006   CAISSE de 6                   20,82
     2.008   Bouteille                      5,35
     2.007   Bouteille                    278,15
     2.008   CAISSE de 6                   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 


   Nous allons ajouter les nouvelles clauses : ROLLUP et CUBE

 SELECT annee , cav_format, sum(prxactuel)
   FROM ma_cave join vins using(vin_code)
  GROUP BY ROLLUP (annee , cav_format)


 affiche le total par année/format, puis par année, puis le total général :


     ANNEE   CAV_FORMAT                      SUM 
     2.006   Bouteille                    278,78
     2.006   CAISSE de 6                   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 6                   15,76
     2.008   -                             21,11
         -   -                            627,82




     Vous pourriez compléter le select par un ORDER BY

 SELECT annee , cav_format, sum(prxactuel)
   FROM ma_cave join vins using(vin_code)
  GROUP BY CUBE (annee, cav_format)


 affiche tous les totaux de toutes les combinaisons (un cube, donc !)


     ANNEE   CAV_FORMAT                      SUM 
     2.006   Bouteille                    278,78
     2.006   CAISSE de 6                   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 6                   15,76
     2.008   -                             21,11
          -  -                            627,82
          -  CAISSE DE 6                   28,96
          -  CAISSE de 6                   36,58
          -  Bouteille                    562,28

CAS PARTICULIER DES DATES


              DATE(expression)


                  convertit "expression" au format DATE


                  formats admis: TIMESTAMP (extrait la partie DATE)
                                 7 ALPHA   (format julien AAAAJJJ)
                                 ALPHA représentant une date éditée
                                 (AAAA-MM-JJ, par exemple)
                                 numérique représentant le nbr de jours - 1
                                  depuis le 1er janvier de l'AN 1.


              DAY, MONTH, YEAR


                  renvoient la partie concernée d'une expression date
                   (voir ci dessus + variable de type DATE)


              DAYS(expression)
                  renvoie le nombre de jours -1 séparant cette date du
                   1er janvier de l'an 1.

MANIPULATION D'HORAIRES


              TIME(expression)


                  convertit "expression" au format TIME


                  formats admis: TIMESTAMP (extrait la partie TIME)
                                 ALPHA représentant une heure éditée
                                 (HH:MM:SS)


              HOUR, MINUTE, SECOND
                  renvoient la partie concernée d'une expression TIME
                   (voir ci dessus + variable de type TIME)




              TIMESTAMP(expression)
                  convertit "expression" (horodatage édité)


              TIMESTAMP(date heure)
                  produit l'horodatage correspondant (microsec. à zéro)

NOTION DE DUREE:


              Durées explicites


                  YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS, MICROSECONDS


                   servent aux calculs (ARRIVEE + 6 MONTHS + 15 DAYS)


                  ainsi que CURRENT DATE, CURRENT TIME et CURRENT TIMEZONE


                  (CURRENT TIMEZONE représente le décalage avec GMT
                    ==> CURRENT TIME - CURRENT TIMEZONE = temps universel)


              Durées numériques (ou implicites)


                  pour une date  = valeur numérique 8,0 aaaaammjj
                  pour une heure = valeur numérique 6,0 hhmmss
                  pour un horodatage = 20,6 (décimales = microsecondes)


                  CURRENT DATE - DATLIV = 0615 ==> 6 mois et 15 jours

 INCREMENTATION, DECREMENTATION, DIFFERENCE




              AJOUT (+)


                  TOUJOURS Date + durée = date
                           heure + durée = heure


                  si le mois est modifié en dépassant 12, il est ajusté et
                   l'année est incrémentée (idem jours, minutes, secondes)


              RETRAIT (-)


                  DATE - durée = DATE : il s'agit d'une décrémentation


                  DATE - DATE = durée : il s'agit d'une différence


                   la durée est exprimée en durée numérique à l'affichage


                   ainsi 010514 ==> 1 an, 05 mois et 14 jours d'écart.

                   une durée explicite peut être utilisée lors d'un calcul


                     WHERE (DATliv - 2 MONTHS) > DATcde


                   un calcul produit FORCEMENT un résultat de type durée
                     numérique et doit donc être comparé avec une valeur
                     numérique


                     WHERE (DATliv - DATcde) < 200 (soit 2 mois)


 Autres Fonctions DATE : 
 CURDATE()            = idem au registre CURRENT DATE
 CURTIME()            =   "      "       CURRENT TIME
 NOW()                =   "      "       CURRENT TIMESTAMP
 DAYOFMONTH(date)     = jour dans le mois      (idem DAY().)
 DAYOFWEEK(date)      = jour dans la semaine  (1=dimanche)
 DAYOFWEEK_ISO(date)  = jour dans la semaine  (1=Lundi) 
 DAYOFYEAR(date)      = jour (julien) dans l'année.
 QUARTER(date)        = N° du trimestre
 WEEK(date)           = N° de la semaine ATTENTION : 1er Janvier = semaine 1
 WEEK_ISO(date)       = N° de la semaine, 1er janvier = 1 ou 53.

 MONTHNAME(date)      = Nom du mois
 DAYNAME(date)        = Nom du jour
 LAST_DAY(date)       = Fin de mois d'une date
 NEXT_DAY(date, 'DIM')= prochain jour(DIM, LUN, MAR, MER, JEU, VEN, SAM) suivant la date

  JOINTURE : 
                    SI la clause WHERE n'est pas utilisée
                    SQL joint à CHAQUE enregistrement de TABLE1
                    TOUS les enregistrements de TABLE2.
  norme ISO 89:


           +  SELECT NOM, PRENOM, STGTBL.AGENCE, LIBAGE
                     FROM SQLDB/STGTBL, SQLDB/AGETBL
                     WHERE STGTBL.AGENCE = AGETBL.AGENCE


              (la variable AGENCE étant dupliquée il devient
               obligatoire de qualifier la variable par le nom
               de la table "STGTBL.AGENCE")


              OU


           +  SELECT NOM, PRENOM, A.AGENCE, LIBAGE
                     FROM SQLDB/STGTBL A, SQLDB/AGETBL B
                     WHERE A.AGENCE = B.AGENCE


                      (Utilisation des corrélations)

  la jonction norme 92 peut être définie avec la clause JOIN  
      de la manière suivante (disponible depuis la V3R10)




      + produit cartésien:


            SELECT ... FROM table1 CROSS JOIN table2
            (équivalent à l'absence de clause WHERE)




      + jointure conditionnelle


            SELECT ... FROM table1 JOIN table2 ON zone1 = zone2


            (toute expression logique est acceptée après ON)


            les jointures sont par défaut internes
             (elles n'affichent que les enregistrements en correspondance)


            On parle de INNER JOIN (qui est indentique à JOIN seul)

      + jointure externe (OUTER JOIN)


            on parle de LEFT OUTER JOIN quand on désire tous les
             enregistrements du fichier1 (celui à gauche du mot JOIN)
             qu'ils soient ou non en correspondance avec le fichier2



            pour SQL/400 LEFT JOIN est identique à LEFT OUTER JOIN


            les colonnes de table2 sont initialisées à NULL.




            ce qui rend très intéressantes deux fonctions :


               IFNULL() et VALUE() qui assignent une valeur de remplacement
                                    à une colonne contenant la valeur nulle.


               et la clause IS NULL à utiliser dans un test
                                                       (WHERE CODE IS NULL)

exemples :


liste des clients, ayant passé des commandes :


            SELECT codcli, nomcli, numcde, datcde, datliv
                   FROM clients c  JOIN command d
                                ON c.numcli = d.numcli


liste des clients, avec, pour ceux d'entre eux ayant
 passé des commandes, la liste des commandes :


            SELECT codcli, nomcli, numcde, datcde, datliv
                   FROM clients c LEFT OUTER JOIN command d
                                ON c.numcli = d.numcli
                   WHERE ...


liste des stagiaires
 (si l'agence est inconnue on lui attribut la valeur 'invalide')


            SELECT NOM, PRENOM, STGTBL.AGENCE, IFNULL(LIBAGE, 'invalide')
                     FROM STGTBL S LEFT OUTER JOIN AGETBL A
                                ON S.AGENCE = A.AGENCE

      + traitement des enregistrements sans correspondance


            SELECT ... FROM table1 EXCEPTION JOIN table2 ON zone1 = zone2


            ne fournit que les enregistrements de table1 n'ayant pas
             d'équivalence dans table2.






exemple :




liste des stagiaires enregistrés sous une agence inconnue.




            SELECT NOM, PRENOM
                     FROM STGTBL EXCEPTION JOIN AGETBL A
                                ON S.AGENCE = A.AGENCE

V5R10 :


   - RIGHT OUTER JOIN


     jointure externe à partir du fichier de droite du mot-clé JOIN


    select ... from clients RIGHT OUTER JOIN commandes


   (toutes les commandes ,même si le client est inconnu)






   - RIGHT EXCEPTION JOIN


    select ... from clients RIGHT EXCPTION JOIN commandes


   (toutes les commandes dont le client est inconnu)


V5R30 :


     SELECT * from clients JOIN commandes
                USING( numcli )           -> si la zone porte le même nom
                                                  dans les deux fichiers.

AUTRES SELECT
---------------


           +  SELECT DISTINCT AGENCE FROM SQLDB/STGTBL


                              **********
                              * AGENCE *  1 ligne par valeur
                              **********
                              *   01   *
                              *   02   *
                              *   03   *
                              **********


               La distinction se fait sur l'ensemble des colonnes (ligne entière)



  SOUS SELECTIONS
  -----------------
              SQL/400
               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)

     il est possible d'utiliser les corrélations (ALIAS)
        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 fois que
        la date d'entrée change dans la version S du fichier.

      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 :




            SELECT * FROM STGTBL S
                   WHERE NOT EXISTS (SELECT * FROM
                                       AGENCE WHERE AGENCE = S.AGENCE)

 tables dérivées ou Commun Table Expression :


  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 fichiers temporaires dans les cas complexes.


 deux écritures sont donc possibles
 ----------------------------------
                                            ...........................
 WITH correlation AS ( select ... from)     :  on parle de CTE, soit  :
                                            :     COMMON TABLE        :
      SELECT ... from correlation           :      Expression         :
                                            :.........................:
ou


 SELECT ... FROM   (select ... from ...)

exemple , soit le fichier des stagiaires, chaque stagiaire est enregistré sous
             un code agence.


 je veux l'agence ayant le plus de stagiaires
 ....................................................................
 : WITH  temp AS                                                    :
 :  ( select count(*) as nbr from STGTBM group by AGENCE )          :
 :                                                                  :
 : select max(nbr)                                                  :
 :   from temp                                                      :
 :..................................................................:

 +  en 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.