TIPS AS400 IBMi SQL DB2

EXCEPT en SQL permet d’extraire les différences entre 2 tables.

Exemple : 2 tables article avec des contenus différents

1) Extraire les lignes de ARTICLE qui ne sont pas dans ARTICLE2 :
SELECT * FROM article
EXCEPT
SELECT * FROM article2
Résultat : Casquette jaune

2) Extraire les lignes de ARTICLE2 qui ne sont pas dans ARTICLE :
SELECT * FROM article2
EXCEPT
SELECT * FROM article
Résultat : Beret noir

3) Il est possible de comparer sur une ou plusieurs colonnes choisies.
Par exemple, extraire les couleurs de ARTICLE2 qui ne sont pas dans ARTICLE :
SELECT couleur FROM article2
EXCEPT
SELECT couleur FROM article
Résultat : noir

A noter, il est possible de comparer 2 fichiers source avec EXCEPT :
1- OVRDBF FILE(SOURCE1) TOFILE(BIB1/QRPGSRC) MBR(PGM1)
2- OVRDBF FILE(SOURCE2) TOFILE(BIB2/QRPGSRC) MBR(PGM2)
3- SELECT * FROM source1 EXCEPT SELECT * FROM source2

Lister les bibliothèques :
SELECT * FROM sysschemas

Par exemple, les 10 plus grosses bibliothèques :
SELECT * FROM sysschemas
ORDER BY schema_size DESC
LIMIT 10

Lister les tables :
SELECT * FROM systables

Par exemple, lister les tables d’une bibliothèque :
SELECT * FROM systables
WHERE table_schema ='maBib'


Lister les colonnes :
SELECT * FROM syscolumns

Lister les colonnes d’une table :
SELECT * FROM syscolumns
WHERE table_name='maTable' AND table_schema='maBib'

SELECT item ,
LISTAGG(TRIM(couleur), ',') WITHIN GROUP(ORDER BY couleur)
AS couleurs
FROM article
GROUP BY item

Explications :
LISTAGG permet de lister dans une colonne les données issues de plusieurs lignes.
LISTAGG(colonne_à_concaténer , 'séparateur') WITHIN GROUP(ORDER BY colonne_de_tri)

Dans l’exemple donné, le SQL transforme le fichier article suivant

en 2 lignes composées de la colonne item et d’une colonne de couleurs séparées par la virgule

DELETE FROM monfichier a
WHERE RRN(a) NOT IN ( SELECT MAX(RRN(b)) FROM monfichier b
WHERE (a.cle1, a.cle2) = (b.cle1, b.cle2) )

Explications :
La fonction RRN permet de récupérer le numéro d’enregistrement de la ligne.
Le SQL supprime donc les doublons (clé1+clé2 dans l’exemple) qui ne correspondent pas au dernier numéro d’enregistrement.

Si on veut conserver le premier doublon et non le dernier doublon, il faut remplacer MAX par MIN :
DELETE FROM monfichier a
WHERE RRN(a) NOT IN ( SELECT MIN(RRN(b)) FROM monfichier b
WHERE (a.cle1, a.cle2) = (b.cle1, b.cle2) )

Pour simplement chercher les doublons, on peut utiliser :
SELECT * FROM monfichier a
WHERE RRN(a) NOT IN ( SELECT RRN(b) FROM monfichier b
WHERE (a.cle1, a.cle2) = (b.cle1, b.cle2) )