SQL sur AS/400

Le langage SQL est extrêmement complet et puissant. La documentation et les ressources sont nombreuses. Un site intéressant, en français, avec beaucoup de ressources iSeries : http://conduitedeprojet.net

Pour concevoir vos requêtes les utilitaires Query/400, Query Manager et SQL de l'AS/400 sont très adaptés, ils se lancent respectivement par les commandes WRKQRY, STRQM et STRSQL.

Le code de la requête pourra ainsi être simplement copié.

Instructions de base

Sélectionner les données

SELECT FROM

SELECT * FROM t1 (toutes les colonnes)

SELECT c1,c2 FROM t1 (sélection des colonnes c1 et c2)

SELECT DISTINCT c1 FROM t1 (élimine les doublons : ne ramène qu’une valeur pour la colonne c1)

SELECT c1 AS "colonne1" FROM t1 (renommer une colonne)

Restreindre la sélection

SELECT * FROM t1 WHERE

SELECT * FROM t1 WHERE c1 IN (’01’,’02’,’04’)

SELECT * FROM t1 WHERE c2 NOT BETWEEN 10 AND 15

SELECT * FROM t1 WHERE c3 IS NULL

SELECT * FROM t1 WHERE c3 IS NOT NULL

>, >=, <, <=, =, <>, (comparateur arithmétiques)

AND, OR, NOT, (comparateur logique)

% (n’importe quelle séquence de car.)

_ (soulignement) (n’importe quel caractère)

Trier et présenter les résultats

SELECT * FROM t1 ORDER BY c1 (tri ascendant par défaut)

SELECT * FROM t1 ORDER BY c2,c4 (tri par c2 puis tri par c4)

SELECT * FROM t1 ORDER BY c1 ASC, c3 DESC (tri ascendant ou descendant)

Exprimer les jointures

SELECT * FROM t1,t2 (jointure sans qualification = produit cartésien)

SELECT * FROM t1,t2 WHERE t1.c1 = t2.c2 (jointure avec égalité)

SELECT * FROM t1 a,t2 b,t3 c WHERE a.c1=b.c2 AND b.c2=c.c3 (jointures en cascades)

Manipuler les données

SELECT c1,c2*3.25 AS "PRIX" FROM t1

YEAR, MONTH, DATE (date)

SUBSTRING, UPPER, LOWER, CHARACTER_LENGTH (manipulation de chaînes de car.)

Les fonctions statistiques

AVG (moyenne)

COUNT (nombre d’éléments)

MAX (maximum)

MIN (minimum)

SUM (somme)

SELECT COUNT(*)FROM t1

SELECT SUM(c1) FROM t2

Regroupements

SELECT * FROM t1 GROUP BY c1

Sous-requêtes SQL

SELECT * FROM t1 WHERE c1 > (SELECT MIN(c1 FROM t2)

SELECT * FROM t1 WHERE c2 NOT IN (SELECT c2 FROM t2)

SELECT * FROM t1 WHERE c1 > ALL (SELECT c2 FROM t2) (sup. à toutes les valeurs)

SELECT * FROM t1 WHERE c1 > ANY (SELECT c2 FROM t2) (sup. à au moins 1)

Opérateurs ensemblistes

Ils s’intercalent entre deux sélections

UNION (sans les doublons) ou UNION ALL (y compris les doublons)

INTERSECT à partir de la v5r3

EXCEPT à partir de la v5r3

Insérer des enregistrements

INSERT INTO t1 VALUES (’abc’,5,7) (toutes les valeurs doivent être renseignées)

INSERT INTO t1(c1,c2) VALUES (1, ’ROUGE’) (on ne renseigne que les colonnes indiquées, les colonnes non précisées sont mises à NULL ou à la valeur par défaut si elle est précisée)

INSERT INTO t1 SELECT * FROM t2

Mises à jour d’enregistrement

UPDATE t1 SET c2=’ROUGE’ WHERE c1=1

Supprimer des enregistrements

DELETE FROM t1 WHERE c1=1

DELETE FROM t1 WHERE c1 IN (SELECT c2 FROM t2)

DELETE FROM t1 (supprime tous les enregistrements de la table t1)

Query et SQL

La commande RTVQMQRY ALWQRYDFN(*YES) permet de convertir un Query vers un membre de fichier source avec une requête SQL et des commentaires.

Jointures

Le CROSS JOIN effectue un produit cartésien entre le contenu de deux tables.

Le INNER JOIN permet de faire une jointure entre deux tables et de ramener les enregistrements de la première table qui ont une correspondance dans la seconde table.

Le LEFT OUTER JOIN entre deux tables retourne tous les enregistrements ramenés par un INNER JOIN plus chaque enregistrement de la table 1 qui n’a pas de correspondance dans la table 2.

Le RIGHT OUTER JOIN entre deux tables retourne tous les enregistrements ramenés par un INNER JOIN plus chaque enregistrement de la table 2 qui n’a pas de correspondance dans la table 1.

Un EXCEPTION JOIN retourne uniquement les enregistrements de la table 1 qui n’on pas de correspondance dans la table 2.

Limiter le nombre de lignes (V5R1)

FETCH FIRST n ROWS ONLY

Exemples

Récupérer le nom du mois en cours

MAREQUE est une Source de Données

sChain est une chaîne

sChain="SELECT CASE MONTH(CURRENT DATE) WHEN 1 THEN 'Janvier' WHEN 2 THEN 'Février' WHEN 3 THEN 'Mars' WHEN 4 THEN 'Avril' "

sChain+="WHEN 5 THEN 'Mai' WHEN 6 THEN 'Juin' WHEN 7 THEN 'Juillet' WHEN 8 THEN 'Août' WHEN 9 THEN 'Septembre' WHEN 10 THEN 'Octobre' "

sChain+="WHEN 11 THEN 'Novembre' ELSE 'Décembre' END AS mois FROM SYSIBM/SYSDUMMY1 "

HExécuteRequêteSQL(MAREQUE,MaConnexion1,hRequêteSansCorrection,sChain)

HLitPremier(MAREQUE)

Info (MAREQUE.mois)

Client avec le plus grand nombre de commandes

sChain="SELECT CUST_ID , COUNT(*) AS NBRCDE "

sChain+="FROM SP_ORD "

sChain+="GROUP BY CUST_ID "

sChain+="ORDER BY NBRCDE DESC "

sChain+="FETCH FIRST 1 ROWS ONLY"

HExécuteRequêteSQL(mareque,MaConnexion1,hRequêteSansCorrection,sChain)

HLitPremier(mareque)

Info(mareque.NBRCDE)

sChain="WITH TMP AS "

sChain+="( "

sChain+="SELECT CUST_ID, COUNT(*) AS NBRCDE "