| 
Rappels de quelques nouveautés SQL VRAIMENT importantes : V4R20 - Expressions conditionnelles une expression en SQL représente un traitement sur une/plusieurs zones (concaténation/extraction, calcul, ...) il s'agit ici, de pouvoir donner des conditions à ces traitements : ................................................................... : Select cours, texte, case dispo : : when 1 then 'lecture' : : when 2 then 'Impression' : : when 3 then 'Les deux' : : end AS Disponible : : from AF4MBRP1 : :.................................................................: |
|
........................................................................... : Select noart, qtecde, puart, case : : when codepays <> 'F' then puart : : when dep = 97 then puart *1,09 : : else puart *1,206 : : end as TTC : : from cdecli : :.........................................................................: - CAST = fonction de changement de type CAST(expression as type(lg)) select CAST(codchar as DEC(6, 0)) Attention, CAST de numérique en Alpha élimine les zéros de gauche, DIGITS() les conserve |
|
- Sous sélection admise dans l'ordre UPDATE la notion de sous-sélection permettait de mettre un ordre SELECT dans la clause WHERE d'un ordre SQL. 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) Cette synatxe est maintenant (V4R3) acceptée dans la clause SET de UPDATE UPDATE command C set priha = (select pritarif from article WHERE codart = C.codart ) WHERE priha = 0 |
|
mettre à jour le fichier command, mettre dans priha la valeur retournée par la clause select qui dit (je vais chercher 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 ) |
|
Nouveautés SQL en V4R40 (hors orientation objet) 1/ 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) from clients GROUP BY 2 2/ une nouvelle fonction LOWER ou LCASE transforme en minuscules (le contraire de UCASE donc) |
|
3/ 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) SELECT ... from correlation 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... |
