| 
SQL, 2ème NIVEAU + 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 : :........................................................................: |
|
+ 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.
|
|
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. |
|
+ 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. |
|
+ 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) les SELECT/UNION peuvent maintenant être mémorisés dans des vues |
|
****************************************** * * 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 enregistrements 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 | | |
|
V6
VALUES(val1, val2, ...) peut être utilisé à la place de SELECT
rendant possible : SELECT z1, z2, z3 FROM FICHIER WHERE ...
UNION VALUES(val1 , val2, val3)
VALUES peut être aussi utilisé simplement pour tester une fonction :
Sur la ligne de commande SQL, pour voir le résultat
Dans un TRIGGER pour voir si la fonction produit une erreur(interceptable)
On peut passer un ordre SELECT sur le résultat d'un INSERT permettant
ainsi, de retrouver facilement la valeur d'une zone IDENTITY ou d'un
TIMESTAMP, par exemple.
Exemple avec T1 possédant Z1 AS IDENTITY et Z3 de type TIMESTAMP
SELECT Z1, Z3 FROM FINAL TABLE
(INSERT INTO T1 (z2, z3) VALUES('test', now() ))
Z1 Z3
3 2008-08-07-09.04.17.455674
|
|
SI vous insérez plusieurs lignes, la clause ORDER BY admet maintenant
INPUT SEQUENCE, demandant à ce que les lignes soit triées dans
l'ordre ou elles ont été insérées
Exemple
SELECT Z1, Z3 FROM FINAL TABLE
(insert into qtemp.toto (z2, z3)
values ('test2' , now() ) ,
('test3' , now() )
)
ORDER BY INPUT SEQUENCE
Z1 Z3
4 2008-08-07-09.57.46.375829
5 2008-08-07-09.57.46.375829
la clause GROUP BY évolue beaucoup pour implémenter des fonctions OLAP
|
|
Soit un fichier CAVE et un select GROUP BY suivant : SELECT year(entreele) , cav_format, sum(prxactuel) FROM ma_cave join vins using(vin_code) GROUP BY year(entreele) , cav_format affichant YEAR CAV_FORMAT SUM 2.006 CAISSE de 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 (un niveau de rupture) Nous allons ajouter les nouvelles clauses : GROUPING SETS ROLLUP CUBE |
|
SELECT year(entreele) , cav_format, sum(prxactuel) FROM ma_cave join vins using(vin_code) GROUP BY GROUPING SETS (year(entreele) , cav_format) affiche le total par année, PUIS le total par format YEAR CAV_FORMAT SUM 2.008 - 21,11 2.006 - 299,60 2.007 - 307,11 - CAISSE DE 6 28,96 - CAISSE de 6 36,58 - Bouteille 562,28 Cette clause admet la syntaxe suivante: SELECT year(entreele), MONTH(entreele), cav_format, sum(prxactuel) FROM ma_cave join vins using(vin_code) GROUP BY GROUPING SETS ( (year(entreele) , cav_format) , (year(entreele) , month(entreele)) ) |
|
YEAR MONTH CAV_FORMAT SUM 2.006 - CAISSE de 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 2.007 8 - 15,59 2.006 7 - 14,40 2.008 3 - 15,76 2.007 3 - 20,82 2.007 9 - 155,48 2.007 2 - 12,19 2.007 1 - 28,96 2.006 11 - 35,00 2.006 4 - 20,82 2.007 6 - 74,07 2.006 9 - 229,38 2.008 5 - 5,35 |
|
SELECT year(entreele) , cav_format, sum(prxactuel) FROM ma_cave join vins using(vin_code) GROUP BY ROLLUP (year(entreele) , cav_format) affiche le total par année/format, puis par année, puis le total général YEAR CAV_FORMAT SUM 2.006 Bouteille 278,78 2.006 CAISSE de 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 year(entreele) , cav_format, sum(prxactuel) FROM ma_cave join vins using(vin_code) GROUP BY CUBE (year(entreele) , cav_format) affiche tous les totaux de toutes les combinaisons (un cube, donc !) YEAR CAV_FORMAT SUM 2.006 Bouteille 278,78 2.006 CAISSE de 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 |
|
Enfin, vous pouvez utiliser la syntaxe suivante : SELECT year(entreele) , month(entreele), cav_format, sum(prxactuel) FROM ma_cave join vins using(vin_code) GROUP BY GROUPING SETS( ROLLUP(year(cav_entreele) , cav_format) , ROLLUP(year(cav_entreele), month(cav_entreele)) ) affiche des totaux par année/format, contenant des sous totaux par année et un total général(ROLLUP), puis la même chose pour le couple année/mois YEAR MONTH CAV_FORMAT SUM 2.006 - Bouteille 278,78 2.006 - CAISSE de 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 |
|
2.006 4 - 20,82 2.006 7 - 14,40 2.006 9 - 229,38 2.006 11 - 35,00 2.006 - - 299,60 2.007 1 - 28,96 2.007 2 - 12,19 2.007 3 - 20,82 2.007 6 - 74,07 2.007 8 - 15,59 2.007 9 - 155,48 2.007 - - 307,11 2.008 3 - 15,76 2.008 5 - 5,35 2.008 - - 21,11 - - - 627,82 nouvelle fonction liée GROUPING(col) indique si la ligne en cours est une ligne de regroupement concernant cette colonne (information utile en programmation) |
|
Exemple SELECT year(entreele) , cav_format, sum(prxactuel) , GROUPING(cav_format) FROM ma_cave join vins using(vin_code) GROUP BY CUBE (year(entreele) , cav_format) YEAR CAV_FORMAT SUM GROUPING 2.006 Bouteille 278,78 0 2.006 CAISSE de 6 20,82 0 2.006 - 299,60 1 2.007 Bouteille 278,15 0 2.007 CAISSE DE 6 28,96 0 2.007 - 307,11 1 2.008 Bouteille 5,35 0 2.008 CAISSE de 6 15,76 0 2.008 - 21,11 1 - - 627,82 1 - CAISSE DE 6 28,96 0 - CAISSE de 6 36,58 0 - Bouteille 562,28 0 |
|
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) 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 stagiaire lu ( la version S du fichier. ) il est possible d'imbriquer 32 sélections (attention à la lisibilité) |
|
On peut tester l'existence d'une information dans une autre table de deux manières: LISTE DES STAGIAIRES AVEC UNE AGENCE INCONNUE SELECT * FROM STGTBL WHERE AGENCE NOT IN (SELECT AGENCE FROM AGETAB) mais cela devient compliqué si la clé est complexe (plus d'une colonne) il faudra alors utiliser la syntaxe suivante : 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. |
|
RAPPEL SUR LA CREATION DE VUE : ---------------------------------- 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, AGENCE, LIBAGE) AS SELECT NOM, PRENOM, X.AGENCE, LIBAGE FROM SQLDB/STGTBL X EXCEPTION JOIN SQLDB/AGETBL Y ON X.AGENCE = Y.AGENCE (jointure par exception, les stagiaires sans agence) et tout ordre SQL ne contenant pas ORDER BY (ni UNION avant V5.2) |
|
Il est possible de créer vos propres fonctions sous SQL : a/ en PL/SQL (L4G propre à SQL, disponible gratuitement en V5) b/ en faisant référence à une routine L3G (GAP par exemple). le plus simple est de référencer une fonction (au sens ILE) dans un *SRVPGM par exemple : CREATE FUNCTION AF4TEST/ECART (DATE, DATE) RETURNS DEC(7, 2) EXTERNAL NAME 'BDVINx/ECARTSRV(ECART)' (1) PARAMETER STYLE GENERAL (2) RETURNS NULL ON NULL INPUT ; (3) (1) fait référence à ecart() dans BDVINx/ECARTSRV (2) le passage de paramètres se fait sans gestion de la val. nulle (3) la fonction retourne val. nulle si un des argument est null. |
|
il est possible, maintenant de passer l'odre suivant : SELECT vin_code, ecart(cav_entreele, cav_sortiele) from bdvinx/MA_cave mais aussi : CREATE VIEW nomvue (CODE, ECART) AS SELECT vin_code, ecart(cav_entreele, cav_sortiele) from bdvinx/MA_cave ou bien CREATE VIEW nomvue AS SELECT vin_code, ecart(cav_entreele, cav_sortiele) as ecart from bdvinx/MA_cave |
|
V4R40 tables dérivées. peuvent éviter des vues temporaires. On peut indiquer un ordre SELECT dans la clause from d'un ordre SELECT ou bien déclarer juste avant le SELECT une "vue temporaire" par WITH. cela permet d'éviter des créations de vues dans les cas complexes. deux écritures sont donc possibles ---------------------------------- ........................... WITH un_nom AS ( select ... from) : on parle de CTE, soit : : COMMON TABLE : SELECT ... from un_nom : Expression : :.........................: ou SELECT ... FROM (select ... from ...) AS un_nom |
|
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 rappelant sur chaque ligne le montant global commandé pour la famille. -> SELECT codart , qte * prix as montant, (select sum(qte * prix) from commandes where famcod = c1.famcod) as totfam FROM commandes c1 |
|
Autre exemple : --------------- vous souhaitez obtenir la liste des commandes en indiquant sur chaque ligne le % représenté par ce montant, dans la famille : Select codart , qte * prix as montant, (select sum(qte * prix) from commandes c2 where c2.famcod = c1.famcod) / (c1.QTE * c1.prix) from commandes c1 quelques restrictions : ---------------------- - Le select doit produire un résultat d'UNE ligne, d'UNE colonne - Il faut attendre la V5R40 pour que GROUP BY et UNION soient valides et que cette colonne puisse être utilisée par ORDER BY. LATERAL 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 |
|
Exemple avec DB2SAMPLE SELECT D.DEPTNO, D.DEPTNAME, EMPINFO.AVGSAL, EMPINFO.EMPCOUNT FROM DEPARTMENT D, (SELECT AVG(E.SALARY) AS AVGSAL,COUNT (*) AS EMPCOUNT FROM EMPLOYEE E WHERE E.WORKDEPT = D.DEPTNO ) AS EMPINFO ==> SQL0206 "La colonne ou la variable globale DEPTNO est introuvable." SELECT D.DEPTNO, D.DEPTNAME, EMPINFO.AVGSAL, EMPINFO.EMPCOUNT FROM DEPARTMENT D, LATERAL(SELECT AVG(E.SALARY) AS AVGSAL,COUNT (*) AS EMPCOUNT FROM EMPLOYEE E WHERE E.WORKDEPT = D.DEPTNO ) AS EMPINFO ==> fonctionne A noter que SELECT deptno, deptname, AVG(salary), COUNT(DISTINCT empno) FROM department LEFT OUTER JOIN employee ON deptno = workdept GROUP BY by deptno, deptname ORDER by 1 ==> donnait le même résultat TABLE peut être utilisé à la place de LATERAL |
|
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) - 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 "pritarif" dans le fichier article, de la ligne ayant le code article lu dans le fichier commande ) |
|
......................................................................... :UPDATE recap R set SALAIRAN = (select sum(salair + primes) : : from personp1 : : where matricule = R.matricule) : :.......................................................................: mettre à jour le fichier recap, mettre dans "salairAN" la valeur retournée par la clause select qui dit : (je vais chercher la somme du calcul (salair + primes) dans le fichier du personnel des lignes ayant le matricule lu dans le fichier RECAP ) l'assignation de valeurs peut se faire de deux manières: UPDATE fichier set (col1, col2) = (select zone1, zone2 from ... where ...) ou UPDATE fichier set col1 = (select zone1 from fichier2 where ...) , col2 = (select zone2 from fichier3 where ...) |
|
L'ORDRE MERGE MERGE INTO cible C USING (SELECT source1 , source2 FROM source) S ON (C.zonecle = S.zonecle) WHEN MATCHED THEN UPDATE SET cible2 = source2 WHEN NOT MATCHED THEN INSERT (cible1, cible2) VALUES(source1, source2) il s'agit de faire comme une jointure entre "cible" et "source" le but du jeu étant de faire une mise à jour ou un ajout dans "cible" MERGE INTO cdes C USING (SELECT codart , qte, prix FROM integration) S ON (C.codart = S.codart) WHEN MATCHED THEN UPDATE SET qte = s.qte, orix = s.prix WHEN NOT MATCHED THEN INSERT (codart, qte, prix) VALUES(S.codart, S.qte, S.prix) |
|
l'ORDRE DELETE DELETE FROM .....table à modifier WHERE ..........sélection (sans WHERE mise a blanc de la table) la clause WHERE supporte les sous-sélections si 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 ) |
|
l'ORDRE TRUNCATE (7.2) produit le même résultat qu'un CLRPFM TRUNCATE TABLE CLIENTP1------> -----------------------------> |-DROP STORAGE-| -----------------------------> |-IGNORE DELETE TRIGGERS-| -----------------------------. |-RESTART IDENTITY-| |
|
FONCTIONS DE VALIDATION INVALIDATION -------------------------------------- 1/ on indique le niveau de validation avec lequel on travail - sur la commande STRSQL (ou par F13) ou sur le paramétrage - sur l'ordre (SELECT par exemple) par : SELECT (..) -------- FROM (..)-----------+--------------+------------> |-WHERE(..)----| >--WITH--+-NC--------------------+-----------------------------------> | | +-UR--------------------+ +-CS--+---------------+-+ | | | | | +-KEEP LOCKS----+ | | | +-RS--------------------+ +-RR--------------------+ |
|
NC : pas de contrôle de validation (==> validation implicite) toutes les autres valeurs indiquent des actions base de données en attente de validation (en suspend) et indiquent les différents niveaux de 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) |
