Principales instructions SQL sur AS400
|
SQL 89 |
SELECT colonne1 [as entete1], |
| SQL 92 (V3R10) |
SELECT colonne1 [as entete1], join = uniquement les enregistrements en correspondance |
| (tables dérivées) |
Soit Soit |
| UPDATE (-> V4R20) |
UPDATE fichier SET colonne1 = valeur1 WHERE [sélection] |
| UPDATE (V4R30) |
UPDATE fichier f SET colonne1 = (select valeur1 from autrefichier where cle = f.cle) WHERE [sélection] |
| DELETE | DELETE FROM fichier WHERE [sélection] |
| INSERT |
INSERT INTO fichier VALUES (valeur1, touteslescolonnes ...) |
Sélections
| Opérateur logique | Exemple(s) | |
|---|---|---|
| colonne ? colonne ou colonne ? valeur |
? |
QTECDE <> 0 LIBART = 'Pamplemousse' PRIX >= 45 |
| IN (val1, val2, val3, ...) | DEPT IN (44, 49, 22, 56, 29) | |
| BETWEEN val1 AND val2 | DEPT BETWEEN 44 AND 85 | |
| LIKE | nom LIKE 'DU%' (commence par) nom LIKE '%PON%' (contient) nom LIKE '%RAND' (se termine par ) |
|
| et aussi OR , AND , NOT , ( , ) . | CODART = 1245 or LIBART = 'Pamplemousse' | |
Fonctions valides (fonctions de groupe)
| Fonction(x) | Retourne ? | Exemple |
|---|---|---|
| AVG(x) | la moyenne de X pour un groupe de ligne | AVG(quantite) |
| COUNT(*) | le nombre de lignes sélectionnées | |
| COUNT(DISTINCT X) | le nombre de valeurs différentes pour X | COUNT(dictinct nocli) |
| MAX(X) | retourne la plus grande valeur sélectionnée | MAX(DATLIV) |
| MIN(X) | retourne la plus petite valeur sélectionnée | MIN(prix) |
| SUM(x) | retourne la somme de X | SUM(qte * prix) |
| VAR(X) | retourne la variance | |
| STDDEV(X) | retourne l'écart type |
Fonctions valides (ligne à ligne)
| Fonction(x) | Retourne ? | Exemple |
|---|---|---|
| MAX(X,Y) | retourne la plus grande valeur de X ou de Y | MAX(prixHA, pritarif) * qte |
| MIN(X,Y) | retourne la plus petite valeur de X ou de Y | MIN(datce, datliv) |
| ABSVAL(x) | la valeur absolue de x | ABSVAL(prix) * qte |
| DEC(x , l, d) | x au format numérique packé avec la lg et la précision demandée. | DEC(zonebinaire) DEC(avg(prix), 9, 2 ) |
| DIGITS(x) | x en tant que chaîne de caractères | DIGITS(datnum) |
| CHAR(x) | x en tant que chaîne de car. (x étant une date) | CHAR(current date) |
| FLOAT(x) | x au format "virgule flottante" | FLOAT(qte) |
| INT(x) | x au format binaire | INT(codart) |
| ZONED(x) | x au format numérique étendu | ZONED(prix) |
| CAST(x as typeSQL[lg]) |
x au format indiqué par typeSQL : types valides |
CAST(qte AS CHAR(9)) Attention les zéros de gauche sont éliminés CAST(prixchar as NUMERIC(7, 2)) cast('123456,89' as numeric(8, 2)) fonctionne cast('123456,89' as numeric(7, 2)) donne une erreur (trop peu d'entiers) |
| STRIP(x) ou TRIM(x) RTRIM(x) LTRIM(x) |
supprime les blancs au deux extrémités de x. • les blancs de droite • les blancs de gauche |
TRIM(raisoc) |
|
LENGTH(x) |
la longueur de x | LENGTH(nom) LENGTH(TRIM(nom)) |
| CONCAT(x , y) | concatene X et Y ( aussi x CONCAT y ou X !! Y) | CONCAT(nom, prenom) |
| SUBSTR(x, d, l) | extrait une partie de x depuis D sur L octets | SUBSTR(nom, 1, 10) SUBSTR(nom, length(nom), 1) |
| LEFT(x, l) | extrait une partie de x depuis 1 sur L octets | LEFT(nom, 10) |
| MOD(x, y) | le reste de la division de x par y | MOD(annee, 4) |
| RRN(fichier) | N° de rang | RRN(clientp1) |
|
TRANSALTE(x) |
X en majuscule | WHERE UCASE(RAISOC) LIKE 'VO%' |
| LOWER(x) LCASE(x) |
x en minuscule (V4R40 uniquement) | WHERE LCASE(ville) LIKE 'nan%' |
| TRANSLATE( x, remplact, origine) | Remplace tous les caractères de X présent dans origine par le carctère de même position dans remplact | TRANSLATE(prixc, ' F', '0$') remplace $ par F et 0 par sp |
| VALUE(x, y) IFNULL(x, y) |
retourne x s'il est non null, sinon y | IFNULL(DEPT, 0) |
| LOCATE(x, y ,d) | retourne la position à laquelle x est présent dans y ou 0 (la recherche commence en D, qui est facultatif) | LOCATE(' ', raisoc) |
| POSITION(x IN y) |
idem LOCATE | POSITION(' ' IN raisoc) |
| NULLIF(x, y) | reourne NULL si X = Y | NULLIF(Prix, 0) |
| CASE when ... then .. when ... then .. [else] END |
retourne la valeur du premier THEN ayant la clause WHEN de vérifiée. |
CASE dept ou bien CASE |
Cas particulier des dates
- On ne peut utiliser l'arithmétique temporelle qu'avec des dates, des heures, des horodatages
- les calculs peuvent se faire sous la forme
- date + durée = date
- date - durée = date
- date - date = durée
- heure + durée = heure
- etc ..
- les durées peuvent être exprimées de manière explicite avec
-
YEARS MONTHS DAYS HOURS MINUTES SECONDS
- les durées résultat (datcde - datliv) seront toujours exprimées sous la forme AAAAMMJJ, où :
-
AAAA represente le nombre d'années MM le nombre de mois JJ le nombre de jours
-
- Ainsi 812 signifie 8 mois, 12 jours
- 40301 signifie 4 ans , 03 mois, 01 jour
Fonctions liées aux dates
| Fonction(x) | Retourne ? | Exemple |
| DATE(x) X doit être une chaîne au format du JOB |
une date (sur laquelle les fonctions suivantes s'appliquent) | DATE( substr(digits(dat8), 7, 2) concat '/' concat substr(digits(dat8), 5, 2) concat '/' concat substr(digits(dat8), 3, 2) ) |
| DAY(D) DAYOFMONTH(D) |
retourne la partie jour de D (doit être une date ou un écart AAAAMMJJ). |
DAY(DATCDE) |
| MONTH(D) | retourne la partie mois de D (idem) |
MONTH(current date) |
| YEAR(D) | Retourne la partie année de D (idem) | YEAR(current date - DATCDE) |
| DAYOFYEAR(D) | retourne le n° de jour dans l'année (julien) | DAYOFYEAR(datdep) |
| DAYOFWEEK(D) | retourne le N° de jour dans la semaine (1 = Dimanche, 2=Lundi, ...) |
DAYOFWEEK(ENTRELE) |
| DAYS(D) | retourne le nbr de jours depuis 01/01/0001 | DAYS(datcde)- DAYS(datliv) |
| QUARTER(D) | retourne le n° du trimestre | QUARTER(DATEFIN) |
| WEEK(D) | retourne le n° de semaine (Attention 01/01/xx donne toujours semaine 1) |
WHERE WEEK(DATLIV)= WEEK(DATCDE) |
| CURDATE() | retourne la date en cours, comme CURRENT DATE | |
| CURTIME() | retourne l'heure en cours, comme CURRENT TIME | |
| NOW() | retourne le timestamp en cours |
Fonctions liées aux heures
| Fonction(x) | Retourne ? | Exemple |
|---|---|---|
| TIME(T) | une heure | TIME( substr(digits(h6), 1, 2) concat ':' concat substr(digits(h6), 3, 2) concat ':' concat substr(digits(h6), 5, 2) ) |
| HOUR(T) | retourne la partie heure de T | HOUR(Pointage) |
| MINUTE(D) | retourne la partie minute de T | |
| SECOND(T) | Retourne la partie secondes de T |
Fonctions liées aux Timestamp
| Fonction(x) | Retourne ? | Exemple |
|---|---|---|
| TIMESTAMP(T) | un timestamp (date - heure - microsecondes) | TIMESTAMP(' 1999-10-06.15.45.00.000001 ') |
| TIMESTAMP (D T) |
un timestamp (microsecondes à 0) | TIMESTAMP(datcde heure) |
| plus toutes les fonctions liées aux dates et aux heu res |
Sous sélections, Ordre SQL intégré dans la clause WHERE d'un ordre SQL :
| SELECT * FROM tarif WHERE prix < (SELECT AVG(prix) from tarif) |
donne la liste des articles ayant un prix inférieur à la moyenne
| SELECT * FROM tarif WHERE prix BETWEEN (SELECT AVG(prix)*0,9 from tarif)AND (SELECT AVG(prix)*1,1 from tarif) |
donne la liste des articles ayant un prix variant d'au maximum +/- 10 % par rapport à la moyenne
| SELECT * FROM tarif T WHERE prix < (SELECT AVG(prix) from tarif Where famille = t.famille ) |
donne la liste des articles ayant un prix inférieur à la moyenne de leur famille
- vous pouvez aussi utiliser la clause EXISTS dans un SELECT imbriqué.
• Elle indique VRAI si le select imbriqué retourne une ligne ou plus
• Elle indique FAUX si le select imbriqué ne retourne aucune ligne.
soit un fichier article ayant une colonne "unité_stockage" et un fichier stock,
il s'agit de supprimer les articles dans le fichiers stock si la zone "unité_stockage" est à blanc dans le fichier article.
DELETE from stock S where exists (SELECT * from articles where codart = S.codart
and "unité_stockage" = ' ')