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 "
sChain+="FROM SP_ORD "
sChain+="GROUP BY CUST_ID "
sChain+=") "
sChain+="SELECT * FROM TMP "
sChain+="ORDER BY NBRCDE DESC "
sChain+="FETCH FIRST 1 ROWS ONLY"
HExécuteRequêteSQL(mareque,MaConnexion1,hRequêteSansCorrection,sChain)
HLitPremier(mareque)
Info(mareque.NBRCDE)