Quelques requêtes simples, mais pratiques
Soit TABCDE la table des commandes clients.
La table TABCDE cotient toutes les entêtes de commandes client. CODCLI est le code client.
La table TABCLI cotient les clients. CODCLI est le code client. LIBCLI est le nom du client.
Comment sélectionner à partir d’un fichier entête de commandes, le code client qui à commandé le plus, et le nombre de commandes associées ?
1ère solution
SELECT CODCLI , COUNT(*) AS NBRCDE
FROM TABCDE
GROUP BY CODCLI
ORDER BY NVRCDE DESC
FETCH FIRST 1 ROWS ONLY
2nde solution
WITH TMP AS
(
SELECT CODCLI , COUNT(*) AS NBRCDE
FROM TABCDE
GROUP BY CODCLI
)
SELECT * FROM TMP
ORDER BY NBRCDE DESC
FETCH FIRST 1 ROWS ONLY
La seconde solution apporte plus de souplesse et de clarté, si vous souhaitez compliquer le second select, pour récupérer le libellé du client sélectionné par exemple :
WITH TMP AS
(
SELECT CODCLI , COUNT(*) AS NBRCDE
FROM TABCDE
GROUP BY CODCLI
)
SELECT CODCLI, NBRCDE, LIBCLI FROM TMP
LEFT
JOIN TABCLI
ON TMP.CODCLI = TABCLI.CODCLI
ORDER BY NBRCDE DESC
FETCH FIRST 1 ROWS ONLY
Comment sélectionner dans un fichier ligne de commandes, la 1ère ligne de chaque commandes
SELECT CODE A, LIBEL A FROM FICHIER A
WHERE RRN(A) IN(SELECT MAX(RRN(B))
FROM FICHIER B WHERE
A.CODE = B.CODE)
ORDER BY CODE
Pour avoir la dernière ligne créée on remplace MAX par MIN dans la requête.
Comment sélectionner l’article ayant la meilleure moyenne
SELECT CODART, AVG(QTE) AS QTE
FROM ARTICLE GROUP BY CODART
ORDER BY QTE DESC
FETCH FIRST 1 ROWS ONLY
Comment afficher la liste des articles ainsi que le nombre de commandes par code article
SELECT A.CODART , A.LIBART , COALESCE(B.NBR , 0)
FROM ARTICLE A
LEFT OUTER JOIN
(SELECT CODART , COUNT(NUMCDE) AS NBR
FROM COMMANDES
GROUP BY CODART ) AS B
ON B.CODART = A.CODART
Comment supprimer le zéros non significatifs à droite et à gauche
SELECT STRIP(TRIM(CHAR(VALEUR)), T, ‘0’)
FROM FICHIER
Comment formater un nombre en 4 zones (signe, partie entière, caractère decimal, partie décimale). Pour transfert via des interfaces par exemple. Si, si, certains utilisent ce système.
Exemple: extraction du montant MTCDE ( au format 9 dont 3)de chaque commande client en 4 zones :
‘+’ ou ‘-‘ selon que le montant est positif ou négatif
la partie entière
le caratère décimal
la partie décimale
SELECT
CODCLI
CASE SIGN(MTCDE)
WHEN -1 THEN '-'
ELSE '+'
END SIGNE
CAST(
MTCDE AS DEC(6, 0)
) AS MTENT ,
'.' ,
CAST(
(MTCDE - CAST(MTCDE AS DEC(6, 0))) * 1000 AS DEC(3, 0)
) AS MTDEC
FROM TABCDE
Comment effectuer des comptages sur deux (ou plus) tables ?
Par exemple, le total des enregistrement des tables TABLE1 et TABLE2 pour le flag ACTIF est égal à ‘O’.
Attention, sur AS400, uniquement à partir de la V5R2.
SELECT
COUNT(*) + (
SELECT COUNT(*) FROM TABLE2
WHERE ACTIF = ‘O’ )
FROM TABLE1
WHERE ACTIF = ‘O’
Supprimer les caractères doublés dans un libellé
Exemple : On veut remplacer toutes les occurences « » par »
« Produit « »A » » » doit devenir « Produit « A » »
UPDATE TABLE
SET ZONLIB = SUBSTR(ZONLIB , 1,
LOCATE(‘ » »‘, ZONLIB, 2) ) CONCAT
SUBSTR(ZONLIB , LOCATE(‘ » »‘, ZONLIB, 2) + 2 ,
LENGTH(ZONLIB) – LOCATE(‘ » »‘, ZONLIB, 2) + 1 )
WHERE ZONLIB LIKE ‘% » »%’
Cette requête doit être lancée plusieurs fois pour prendre en compte les doublons répétés.
Comparer rapidement la structure de deux tables
SELECT
A.DBIFIL AS TABLE
, B.DBIFIL AS TABLE
, A.DBIFLD AS CHAMP
, B.DBIFLD AS CHAMP
, A.DBIPOS AS POSITION
, B.DBIPOS AS POSITION
, A.DBITXT AS TEXTE
, B.DBITXT AS TEXTE
, A.DBITYP AS TYPE
, B.DBITYP AS TYPE
, COALESCE(CAST(A.DBICLN AS CHAR(4)), CAST(A.DBINLN AS CHAR(4))) AS LNG1
, COALESCE(CAST(B.DBICLN AS CHAR(4)), CAST(B.DBINLN AS CHAR(4))) AS LNG1
, COALESCE(CAST(A.DBINSC AS CHAR(1)), ‘ ‘) AS LNG2
, COALESCE(CAST(B.DBINSC AS CHAR(1)), ‘ ‘) AS LNG2
FROM QADBIFLD A
LEFT OUTER JOIN QADBIFLD B
ON B.DBIFIL = ‘TABLE1’
AND B.DBILIB = ‘REFDTA’
AND B.DBIPOS = A.DBIPOS
WHERE
A.DBIFIL = ‘TABLE2’
AND A.DBILIB = ‘REFDTA’
ORDER
BY A.DBIFIL, B.DBIFIL ,
A.DBIPOS, B.DBIPOS
Vote table a subit des modification de données et vous ne vous y retrouvez plus ?
Exemple avec les tables TABLE1 et TABLE2 dont la structure est identique :
Les champs C1, C2 et C3 qui constituent la clé unique du fichier
Le champ C4
La requête sql suivante vérifie si le contenu des deux fichiers sont identiques à rrn égal.
SELECT COUNT(*)
FROM TABLE1 A , TABLE2 B
WHERE RRN(A) = RRN(B)
AND A.C1 <> B.C1
OR A.C2 <> B.C2
OR A.C3 <> B.C3
OR A.C4 <> B.C4
La requête SQL suivante isole les éléments dont la clé est identique, mais qui diffèrent dans la valeur des zones associées.
WITH TMP AS (
SELECT A.C1, A.C2, A.C3, A.C4 AS VAL1, B.C4 AS VAL2
FROM TABLE1 A
JOIN TABLE2 B
ON B.C1 = A.C1
AND B.C2 = A.C2
AND B.C3 = A.C3
)
SELECT * FROM TMP
WHERE VAL1 <> VAL2
Savoir si une zone alpha ne contient que des numériques
SELECT count(*) FROM Table
WHERE TRANSLATE(C1, ‘ ‘ , ‘1234567890’) = ‘ ‘
Cette requête renvoie le nombre d’ enregistrements dont la zone C1 (qui est alphabétique) ne contient que des caractères numériques.
Autres exemples sur le site
Des couleurs dans le résultat d’une requête SQL
Créer un fichier Excel à partir d’une requête SQL
Sélections sur les dates
Comment gérer dans une requêtes SQL, des notions qui ne figurent pas dans une table ?
297
298