| 
SSSSSSSSS QQQQQQQQQ LL 44 000000 000000 SS QQ QQ LL 44 44 00 00 00 00 SSSSSSSSS QQ QQ LL === 44444444 00 00 00 00 SS QQ QQ LL 44 00 00 00 00 SSSSSSSSS O QQQQQQQQQ O LLLLLLL 44 000000 000000 Q -------------------------------------------------------------------- STRUCTURED QUERY LANGUAGE = Langage structuré d'interrogation |
|
A/ TERMINOLOGIE . ----------------- SELECTION PROJECTION JONCTION, INTERSECTION, DIFFERENCE SELECTION (stagiaires de l'agence 01) .................................................. : NUMSTG : 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 : 04 : :..................:........: |
|
JONCTION (sur agence) ......................................................... : NUMSTG : NOM : PRENOM : AGENCE : LIBAGE : :........:............:..........:........:.............: : 101 : DUBOIS : Eric : 01 : LILLE : : 107 : ERNST : Patrick : 01 : LILLE : : 110 : DUPONT : Alain : 02 : NANTES : : 102 : MERCIER : Anne : 03 : DAX : : 104 : BOZUFFI : Ricardo : 03 : DAX : : 121 : GARDEL : Sophie : 01 : LILLE : : 130 : FLAVARD : Cecile : 02 : NANTES : : 132 : GOUDE : Jean : 02 : NANTES : : 103 : FORTRAN : Yves : 03 : DAX : :........:............:..........:........:.............: |
|
INTERSECTION (si l'agence existe) ........................................................... : NUMSTG : NOM : PRENOM : AGENCE : NOTE: ENTREE : :........:.............:..........:........:.....:........: : 101 : DUBOIS : Eric : 01 : 07 : 890405 : : 107 : ERNST : Patrick : 01 : 12 : 891215 : : 110 : DUPONT : Alain : 02 : 14 : 890405 : : 102 : MERCIER : Anne : 03 : 18 : 900302 : : 104 : BOZUFFI : Ricardo : 03 : 12 : 900302 : : 121 : GARDEL : Sophie : 01 : 17 : 891215 : : 130 : FLAVARD : Cecile : 02 : 09 : 890405 : : 132 : GOUDE : Jean : 02 : 13 : 890405 : : 103 : FORTRAN : Yves : 03 : 17 : 900302 : :........:.............:..........:........:.....:........: |
|
DIFFERENCE (si l'agence n'existe pas) ........................................................... : NUMSTG : NOM : PRENOM : AGENCE : NOTE: ENTREE : :........:.............:..........:........:.....:........: : 105 : DUBROVNIK : Marie : 04 : 16 : 891215 : :........:.............:..........:........:.....:........: |
|
B/ commande STRSQL ------------------ STRSQL COMMIT(*NONE) (contrôle de validation) *NONE pas de contrôle de validation ^ *CHG contrôle de validation actif | seuls les enreg modifiés, ajoutés journalisation | supprimés sont verrouillés obligatoire | *CS idem *CHG plus verrouillage du dernier enreg | sélectionné (pour les tables en consultation) | *ALL contrôle de validation actif | tous les enreg modifies, ajoutes | supprimes ET SELECTIONNES V sont verrouillés NAMING(*SYS) (convention d'appellation) *SYS convention d'appellation système (LIB/OBJET) un objet non qualifié est recherché dans *LIBL |
|
*SQL convention d'appellation SQL (LIB.OBJET) un objet non qualifie est recherché dans une bibliothèque ayant le nom du USER (sur 8 c) PROCESS(*RUN) (type d'exécution) *RUN les instructions sont exécutees *VLD seules la syntaxe et la validité des instructions sont verifiées *SYN seule la syntaxe est verifiée LIBOPT(*LIBL) (liste de bibliothèque) Liste de bibliothèque utilisee par F16-F17-F18 *LIBL liste de bibliothèques du travail *USRLIBL partie utilisateur de *LIBL *ALLUSR toutes les bibliothèques user *ALL toutes les bibliothèques *CURLIB la bibliothèque en cours -NOMBIB- une bibliothèque donnée |
|
LISTTYPE(*ALL) (type de liste) type d'objets affichés par F16-F17-F18 *ALL tous les objets *SQL seuls les objets SQL (collection,table,vue,index) 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. |
|
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. |
|
fonctions valides: fonctions de changement de genre DIGITS(exp) représentation en alphanumérique d'une expression numérique 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) binaire vers packé : DECIMAL(ZONBIN, 6, 0) FLOAT(exp) représent. en virgule flottante d'une expression numérique |
|
INTEGER(exp) représent. en numérique binaire d'une expression numérique ZONED(exp) représent. en numérique étendu d'une expression numérique CHAR(date) renvoie la représentation caractère d'une date. (séparateurs inclus, suivant le format en cours.) VARCHAR(expression, Lg----------------) ! ! !--CCSID---! Transforme une expression à lg fixe en colonne à lg variable avec choix du code page (CCSID) VARGRAPHIC( ) idem en DBCS (idéogrammes orientaux ou DBCS) |
|
BLOB(exp) transforme un chaine de caractères en BLOB. CHAR(exp) transforme en colonne à taille fixe un expression variable. BINARY() et VARBINARY() liées aux nouveaux types de données v5r30, proches du BLOB, les données étant sans notion de CCSID. la fonction CAST vient remplacer toutes les fonctions précédentes en proposant d'indiquer clairement le nouveau type et la longueur. CAST(expression AS type[lg]) V4R20 ATTENTION : CAST qui convertit du numérique en caractère remplace les zéros non significatifs (ceux de gauche) par des blancs, ce que ne fait pas la fonction DIGITS, qui conserve donc une particularité. |
|
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 équivalent code-page associé à une donnée (france = 297) |
|
Autres fonctions: 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. écriture V3R60 : 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 (V5R30) 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 (zone à lg variable) ex :CHARACTER_LENGTH(trim(nom)) OCTET_LENGTH indique le nombre d'octets occupés par une colonne (V5R3) BIT_LENGTH() indique le nombre de bits occupés par une colonne (V5R3) TRANSLATE(exp) conversion minuscule/MAJUSCULE d'une chaîne ou UCASE( ) ou UPPER( ) mais aussi : |
|
TRANSLATE(chaîne-----------------------------------------------------> ! ! !---, remplacement ---------------------------! ! ! !-- , origine -----! >-------------------------------) ! ! !--PADDED WITH------! Exemple: TRANSLATE(var1 ,' ,F' , '0.$') remplace 0 par ' ' "." par "," et "$" par "F" . LOWER( ) ou LCASE( ) 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 TRAILING Alternative à TRIM (et aussi RTRIM et LTRIM) supprime le(s) caractère(s) "C" (dft = "espace") à gauche (LEADING), à droite (TRAILING) ou les deux (BOTH), de la chaîne indiquée. le résultat est de longueur variable. 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] |
|
REPEAT(c , nb) repète le caractère 'c', nb fois (V5R3) REPLACE(zone, org, new) remplace org par new dans zone (V5R3) INSERT(Z , deb, nb, chaine) (V5R3) insert 'chaine' à partir de 'deb' en remplacant nb caractères (0 = insertion pure). Exemple : sur une zone char(1) contenant '*' Insert(zone1 , 1 , 0 , 'XX') --> 'XX*' --on insert devant Insert(zone1 , 1 , 1 , 'XX') --> 'XX' --on remplace Insert(zone1 , 2 , 0 , 'XX') --> '*XX' --on insert derrière |
|
ENCRYPT_RC2(data, pwd , hint) (V5R3) Encrypte les données founies en premier argument en utilisant le deuxième comme clé, selon l'algorithme RC2. le mot de passe peut être fixé aussi par SET ENCRYPTION PASSWORD, avant. l'asctuce (facultative) est un "pense-bète" mémorisé avec la donnée. V5R4 ENCRYPT_TDES(data , pwd, hint) comme ENCRYPT_RC2 mais en utilisant le cryptage "Triple DES" l'astuce (facultative) est un "pense-bète" mémorisé avec la donnée. GET_HINT() retourne l'astuce (le pense-bète) permettant de se souvenir de la clé (qui est obligatoire avec les fonctions ci-dessous) |
|
DECRYPT_BIT() décrypte une donnée cryptée et retourne du VARCHAR /BIT DATA DECRYPT_BINARY() décrypte une donnée cryptée et retourne du BINARY DECRYPT_CHAR() décrypte une donnée cryptée et retourne du VARCHAR simple DECRYPT_DB() décrypte une donnée cryptée et retourne du Double Byte (UTF-8) ABSVAL(exp) renvoie la valeur absolue d'une expression numérique SQRT(exp) renvoie la racine carrée (voir aussi sinus, cosinus,...) |
|
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 RAND() retourne un nombre aléatoire (< à 1) 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) arrondi inférieur d'une expression numérique avec choix de la precision. TRUNCATE(2,42 , 1) = 2,4 TRUNCATE(2,56 , 1) = 2,5 MULTIPLY_ALT() alternative à l'opérateur *, travaille avec une plus grande précision, à utiliser avec des résultats intermédiaires de plus de 63 chiffres ! (V5R3) RRN(nom-table) renvoie le numéro de rang. DATABASE() retourne le nom du serveur comme CURRENT SERVER (V5R3) |
|
Gestion de la valeur nulle la valeur nulle (la différence entre un prix à 0 et un prix non renseigné) est bien intégrée à SQL. elle devient vite indispensable avec la gestion des dates (date de départ dans le fichier du personnel, par ex) elle est par défaut lors du CREATE TABLE et peut être précisée en SDD par le mot-clé ALWNULL. les tables contenant ces zones doivent être manipulées en RPG4 ou SQL les tests se font sous la forme : DAT_DEAPRT IS NULL PRIX IS NOT NULL |
|
VALUE(exp1, exp2 [,exp...] ) COALESCE et IFNULL alias de VALUE (IFNULL est limité à 2 arguments) renvoient la première valeur non nulle de la liste des expressions Intéressant sous la forme IFNULL(NOM, 'non précisé') pour attribuer une valeur par défaut si NOM est NULL. particulièrement lors des jointures non abouties, les colonnes de la table de droite sont initilaisées à NULL NULLIF(argument1, argument2) retourne NULL, si les deux arguments sont égaux. pour remplacer una valeur significative par la val. nulle. |
|
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 (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 (date +heure +6 décimales) TIMESTAMP(expression) convertit "expression" (horodatage édité) TIMESTAMP(date heure) produit l'horodatage correspondant (microsec. à zéro) |
|
NOTION DE DUREE: + Durées explicites basées sur un chiffre et un mot clé lui donnant du sens. - YEARS, MONTHS, DAYS - HOURS, MINUTES, SECONDS, MICROSECONDS servent aux calculs (ARRIVEE + 6 MONTHS + 15 DAYS) ainsi que - CURRENT DATE - CURRENT TIME - CURRENT TIMEZONE (fuseau horaire) (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 => AAAA MM JJ ainsi 615 (0000.06.15) = 6 mois et 15 jours pour une heure = valeur numérique 6,0 => HH MM SS pour un horodatage = 20,6 (décimales = microsecondes) 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 , 5 mois et 14 jours. 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 (aaaammjj ou hhmmss). 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) [V5R10] 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. [V5R10] DAYNAME(Date) retourne le nom du jour (en Français) de Date (V5R3) MONTHNAME(Date) retourne le nom du mois (en Français) de Date (V5R3) EXTRACT(DAY from zonedate) extrait la partie JOUR de zone date, on peut demander : DAY, MONTH, YEAR d'une date HOUR, MINUTE SECOND d'une heure TIMESTAMP_ISO() convertit en TIMESTAMP : une date (l'heure est à 00:00:00) ou une heure(la date est à aujourd'hui) |
|
V5R40
LAST_DAY(date) retourne la date correspondant au dernier jour du mois
par exemple LAST_DAY('2006-04-21') = 2006-04-30
ADD_MONTHS(date, nbr) ajoute un nombre de mois à la date
Attention, si la date est au dernier jour du mois, la date
calculée est aussi au dernier jour du mois
par exemple DATE('2006-04-30') + 1 months = 2006-05-30
ADD_MONTHS('2006-04-30' , 1) = 2006-05-31
GENERATE_UNIQUE() genère une valeur unique de type CHAR(13)
(basée sur le timestamp en cours)
la fonction TIMESTAMP() peut-être utilisée pour
convertir en clair la valeur générée.
|
|
NEXT_DAY(date , 'JOUR')
retourne le timestamp de la prochaine date ayant le
jour demandé (sur 3 c ou 10c.) à partir de "date".
valeurs admises :
'MON' ou 'LUN' ou 'LUNDI' pour le prochain Lundi
'TUE' ou 'MAR' ou 'MARDI', prochain Mardi
'WED' ou 'MER' ou 'MERCREDI', prochain Mercredi
'THU' ou 'JEU' ou 'JEUDI', prochain Jeudi
'FRI' ou 'VEN' ou 'VENDREDI', prochain Vendredi
'SAT' ou 'SAM' ou 'SAMEDI', prochain Samedi
'SUN' ou 'DIM' ou 'DIMANCHE', prochain Dimanche
Exemple :
NEXT_DAY('2006-12-31' , 'DIM') => ' 2007-01-07-00.00.00.000000'
|
|
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 |
|
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 : :........................................................................: |
|
+ nouvelles fonctions V4R40, liées aux DataLink :
DLVALUE ('HTTP://S44R7480/AF4DIR/AF4SRC/cours.htm', URL, 'mon commentaire')
permet de renseigner un champ DataLink (ici, un lien vers "cours.htm")
DLURLCOMPLETE( ) -> retourne le lien complet vers le fichier stream.
......................................................
: 'HTTP://S44R7480/AF4DIR/AF4SRC/*****cours.htm' :
: ('*****' est le token pour les permissions DB) :
:....................................................:
DLURLPATH( ) -> /AF4DIR/AF4SRC/*****cours.htm
DLURLPATHONLY( ) -> /AF4DIR/AF4SRC/cours.htm
DLCOMMENT( ) -> 'mon commentaire'
DLLINKTYPE( ) -> URL (seule valeur admise aujourd'hui)
DLURLSCHEME( ) -> HTTP: [ou FILE:]
DLURLSERVER( ) -> S44xxxxx
|
|
+ gestion des champs auto-incrémentés (AS IDENTITY) IDENTITY_VAL_LOCAL() retoune la dernière valeur produite pour ce type + fonctions phonétiques 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 http://www.nara.gov/genealogy/soundex/soundex.html s'utilise sous la forme: WHERE SOUNDEX(NOM) = SOUNDEX('HENRI') [=H560] |
|
l'algoritme est le suivant : 1/ conversion en majuscule 2/ conservation du premier caractère 3/ suppression de toutes les occurences des lettres dont la liste suit, à partir de la première occurence: A, E, H, I, O, U, W, Y 4/ attribution d'un code numérique, suivant le type de lettre (labiale, dentale, ....) 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 fançaise voir http://www.chez.com/algor/soundex/soundex.htm ) 5/ élimination de toutes les paires consécutives de chiffres dupliqués. |
|
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.
Le lab 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 très semblables.
|
|
+ nouvelles fonctions OLAP, liées à la V5R40 : ROW_NUMBER() numérote les lignes sur un critère de tri -> select row_number() over (order by nbr desc), pr_code, nbr from stat --nbr de vins/producteur Affiche : ROW_NUMBER PR_CODE NBR 1 2.432 26 2 2.455 26 3 2.486 22 ... 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. |
|
Version 7 Support du type XML et des fonctions suivantes XMLDOCUMENT production d'un flux XML à partir d'une chaine de caractère XMLPARSE production après vérification, d'un flux XML XMLVALIDATE validation d'un flux XML à l'aide d'un schéma XSD XMLTRANSFORM transforme un flux XML à l'aide de XSLT XMTEXT production d'un texte compatible XML XMLELEMENT production d'un élément XML XMLATTRIBUTES production d'un attribut XML XMLNAMESPACES production d'un balise d'espace de nommage XMLPLI production d'une balise processing instruction XMLCOMMENT production d'un commentaire XML XMLCONCAT production d'un flux XML à partir de deux XMLFOREST production d'une suite d'élements XML à partir des colonnes d'une table XMLROW production d'une ligne XML à partir des colonnes d'une table fonctions d'agrégation (récapitulatives) XMLAGG production d'une série d'éléments XML XMLGROUP production d'un flux XML valide. |
|
Fonctions de lecture des fichiers de l'IFS: GET_BLOB_FROM_FILE(chemin , option) retourne un BLOB LOCATOR, sans conversion du CCSID GET_CLOB_FROM_FILE(chemin , option) retourne un CLOB LOCATOR dans le CCSID du job GET_DBCLOB_FROM_FILE(chemin , option) retourne un DBCLOB LOCATOR dans le CCSID DBCS par défaut GET_XML_FILE(chemin) retourne un BLOB LOCATOR en UTF-8, si ce dernier ne possède pas de déclaration XML la fonction l'ajoute. la zone option peut contenir : 0 : les espaces de droite sont conservés 1 : les espaces de droite sont ignorés |
|
EXEMPLES DE SELECTIONS VALIDES : + SELECT * FROM SQLDB/STGTBL (toutes les variables) + SELECT NOM, AGENCE FROM SQLDB/STGTBL (projection) + SELECT CODART, LIBART, QTESTO, PUART, (QTESTO * PUART) as montant FROM SQLDB/STOCKP1 + SELECT MATRIC, NOM, SUBSTR(PRENOM, 1, 8), (SALBRU * 13) FROM SQLDB/PERSONP1 + SELECT NOM, (CURRENT DATE - ARRIVEE) FROM SQLDB/PERSONP1 + SELECT NOM, PRENOM, IFNULL(FONCTION, '*inconnue') 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 (sur 8 c.)
Une fonction peut être testé par VALUES
+ VALUES TRANSLATE('Volubis')
|
|
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 |
|
Jonction: 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 [comme JDFTVAL/SDD ou OPNQRYF JDFTVAL(*YES)] 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.NOSTAG = A.NOSTAG |
|
+ 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 [comme OPNQRYF JDFTVAL(*ONLYDFT)] exemple : liste des stagiaires enregistrés sous une agence inconnue. SELECT NOM, PRENOM FROM STGTBL EXCEPTION JOIN AGETBL A ON S.NOSTAG = A.NOSTAG |
|
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. |
|
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--:variable--------I I--chaine de carac--I I--CURRENT SERVER---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_B 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 (cf CONNECT) :variable, le contenu est comparé avec une variable du pgm. V5R10 : les expressions sont admises dans la clause LIKE. where nom like '%' concat trim(COMMUNE) concat '%' |
|
+ <expression1> IN (<exp2>, <exp3>) Liste de valeurs à comparer ces valeurs pouvant être: I-USER----------I ------I-:variable-----I (cf LIKE) I-constante-----I 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%" |
|
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) |
|
V4R40 les expressions sont maintenant admises sur les clauses GROUP BY 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(bdsit), 1, 2) donne le nombre de clients par département. |
|
SELECT bdist, COUNT(distinct NOCLI) from clients group by substr(digits(bdsit), 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) SELECT codcli, left(raisoc, 20) SELECT codcli, left(raisoc, 20) from clients Ou from clients ORDER BY 2 ORDER BY left(raisoc, 20) |
|
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. |
|
SELECT EN CREATION DE VUE (mémorisation d'un ordre SELECT) --------------------------- CREATE VIEW nomvue AS SELECT NOM, PRENOM FROM SQLDB/STGTBL (projection) CREATE VIEW nomvue (NM, PM) AS SELECT NOM, PRENOM FROM SQLDB/STGTBL (projection , variables renommées) CREATE VIEW nomvue (NOM, PRENOM, STGAGE, LIBAGE) AS SELECT NOM, PRENOM, X.AGENCE, LIBAGE FROM SQLDB/STGTBL X JOIN SQLDB/AGETBL Y ON X.AGENCE = Y.AGENCE (jonction de deux fichiers) Vous pouvez aussi mémoriser des fonctions récapitulatives, utilisables par pgm en lecture séquentielle : |
|
CREATE VIEW nomvue (AGENCE, NBSTAG) AS SELECT AGENCE, COUNT(*) FROM SQLDB/STGTBL GROUP BY AGENCE (traitement récapitulatif) AUTRES SELECT --------------- + SELECT DISTINCT AGENCE FROM SQLDB/STGTBL ********** * AGENCE * 1 ligne par valeur ********** * 01 * * 02 * * 03 * ********** |
|
+ 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 tables. EX: soit la table des formateurs suivante: ****************************************** * AGENCE * NOFORM * NOMFORM * ****************************************** * 01 * 01 * jean-claude * * 01 * 02 * albert * * 01 * 03 * dominique * * 02 * 04 * marie * * 02 * 05 * françois * * 03 * 07 * christine * * 03 * 08 * 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 * 03 * 01 * * nb de formateurs * 02 * 02 * * nb de formateurs * 02 * 03 * * nb de stagiaires * 35 * 01 * * nb de stagiaires * 25 * 02 * * nb de stagiaires * 25 * 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 * 03 * 01 * * nb de stagiaires * 35 * 01 * * nb de formateurs * 02 * 02 * * nb de stagiaires * 25 * 02 * * nb de formateurs * 02 * 03 * * nb de stagiaires * 25 * 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 enregistremen qui se trouvent dans l'une ET PAS dans l'autre des requêtes. 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 : | UNION UNION DISTINCT INTERSECT f1 EXCEPT f2 : A : : A : | A A A D : A : : B : | A B B E : B : : B : | A C C : C : : C : | B D : C : : F : | B E : D : : F : | B F f2 EXCEPT f1 : E : :....: | C F : E : | C :..... | C | D | E | E | F | F | |
|
SOUS SELECTIONS ----------------- SQL/400 (depuis la R 3.0) 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 possible 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 fois que la date d'entrée change dans 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 : SELECT * FROM STGTBL S WHERE NOT EXISTS (SELECT * FROM AGENCE WHERE AGENCE = S.AGENCE) |
|
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) 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. |
|
V4R40 tables dérivées : 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 correlation AS ( select ... from) : on parle de CTE, soit : : COMMON TABLE : SELECT ... from correlation : Expression : :.........................: ou SELECT ... FROM (select ... from ...) |
|
exemple , soit un fichier des cours, chaque cours est enregistré sous 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... |
|
il est possible de placer un SELECT (simple) dans la liste des colonnes d'un Select, depuis la version 5.10 uniquement. 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 rapppellant 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 fonctionne comme une jointure, mais en autorisant dans la requête l'utilisation de colonne(s), venant de niveau supérieur (P.pr_code) select P.pr_nom, V.compteur from producteurs P , LATERAL (select count(*) as compteur from vins where pr_code = P.pr_code ) V |
|
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. ........................................................... : Au passage la clause ORDER BY accepte maintenant : : les ALIAS de colonne (en plus des positions ordinales) : :.........................................................: |
|
Requêtes récursives -------------------- 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 la ligne '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 Select * From Temp ORDER BY tri |
|
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 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. |
|
ATTENTION, ces nouvelles fonctions V5R40 : + fonctions OLAP (rank(), row_number(), etc..) + requêtes recursives + ORDER OF dans ORDER BY + les sous sélections contenant des SELECTS complets (avec UNION ou ORDER BY) + ainsi que les opérateurs EXCEPT et INTERSECT (V5R30) ne sont implémentées que par SQE (le nouveau moteur, disponible en V5R20) les cas suivants impliquent une utilisation de CQE (l'ancien moteur) : -> l'utilisation de TRANSLATE, UPPER/UCASE, LOWER/LCASE -> un tri basé sur SRTSEQ(*LANGIDUNQ) ou STRSEQ(*LANGIDSHR) -> l'utilisation de fonctions TABLE (UDTF) -> l'utilisation de logiques dans la clause FROM -> l'utilisation de tables ayant des logiques avec select/omit (sauf à utiliser IGNORE_DERIVED_INDEX dans QAQQINI) SQE prend à sa charge en V5R40 l'utilisation de LIKE (pas en V5R30) |
|
par exemple, la requête suivante : ---------------------------------- WITH temp (NIVEAU, CPST, CPSE, LIBELLE) as (select 1 , CPST, CPSE, libelle from TBliens where lower(libelle) = 'le cadre' UNION ALL select P.niveau+1 , F.CPST, P.CPST , f.libelle from temp P join TBliens F on F.CPSE=P.CPST ) Select * From Temp génère le code erreur SQL0255 : Fonction incompatible avec la requête. prévoyant 9 codes raison. le notre est le 5 (à cause de la fonction lower) -- Code 5 -- Une expression récursive de table commune n'est pas compatible avec cette requête. |
|
l'ORDRE UPDATE UPDATE ..........table à modifier SET ............les modifs à faire WHERE ..........sélection (sans WHERE maj de toute la table) EXEMPLES DE MISES A JOUR VALIDES UPDATE AGETBL SET LIBAGE = 'Nantes' WHERE AGENCE = 02 UPDATE PERSONP1 SET SALBRU = (SALBRU * 1,02) WHERE COEF = 215 - la clause WHERE supporte les sous-sélections si celles ci portent sur une autre table |
|
- Les sous sélections sont admise dans la clause SET de UPDATE (V4R30) ......................................................................... : : : 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 le 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 ...) , set col2 = (select zone2 from fichier3 where ...) |
|
l'ORDRE DELETE DELETE FROM .....table à modifier WHERE ..........sélection (sans WHERE mise a blanc de la table) EXEMPLES DE SUPRESSIONS VALIDES DELETE FROM STGTBL WHERE AGENCE = 04 DELETE FROM PERSONP1 WHERE MATRIC = 4112 DELETE FROM TABLE (sans where) = suppression de tous les enregistrements |
|
la clause WHERE supporte les sous-sélections si celles 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 ) supprimer les commandes quand la clause SELECT ne trouve pas de ligne (NOT EXISTS) ayant le n° de client lu dans le fichier client (C.nocli) |
|
l'ORDRE INSERT INSERT INTO .....table à modifier (<nom-variable>, <nom-variable>, etc...) si non precisé = toutes les variables VALUES .........valeurs assignées aux variables EXEMPLES D'INSERTIONS VALIDES INSERT INTO STGTBL VALUES(14, 'BREGAY', 'Frederique', 01) OU INSERT INTO STGTBL (NUMSTG, NOM, AGENCE) VALUES(14, 'BREGAY', 01) INSERT INTO STGTEMP SELECT * FROM STGTBL WHERE AGENCE = 03 (le résulat de l'ordre SELECT est place dans la table STGTEMP) |
|
FONCTIONS DE VALIDATION INVALIDATION -------------------------------------- 1/ on indique le niveau de validation avec lequel on travail - sur la commande STRSQL (ou sur l'écran de service par F13) - sur l'ordre SELECT 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 disponiblité 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 ATTENTION, l'ordre SQL COMMIT possède un paramètre important HOLD : - Les ressources sont suspendues. Les curseurs actifs ne sont pas désactivés , les instructions SQL préparées sont conservées et toutes les ressources acquises pendant la transaction sont suspendues. (sans) - Les ressources ne seront pas suspendues. Les curseurs actifs sont désactivés, les instructions SQL préparées sont supprimées et les ressurces suspendues libérées. |
|
Un curseur peut etre déclaré WITH HOLD ou WITHOUT HOLD s'il est déclaré WITH HOLD, il ne sera pas désactivé, meme en cas de COMMIT simple. 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) |
|
La version 6 avait apporté à l'ordre SELECT l'option SKIP LOCKED DATA cela permettait lors d'une lecture avec verrouillage d'enregistrement, c.a.d COMMIT à CS(*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, 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) |
