{"id":372,"date":"2025-10-13T16:54:20","date_gmt":"2025-10-13T16:54:20","guid":{"rendered":"https:\/\/fhix.fr\/?page_id=372"},"modified":"2026-04-07T10:05:11","modified_gmt":"2026-04-07T10:05:11","slug":"tips-as400-ibmi-sql-db2","status":"publish","type":"page","link":"https:\/\/fhix.fr\/index.php\/tips-as400-ibmi-sql-db2\/","title":{"rendered":"TIPS AS400 IBMi SQL DATA DB2"},"content":{"rendered":"<ul class=\"wp-block-latest-posts__list wp-block-latest-posts\"><li><a class=\"wp-block-latest-posts__post-title\" href=\"https:\/\/fhix.fr\/index.php\/2025\/10\/11\/comparer-2-tables-en-utilisant-except\/\">Comparer 2 tables en utilisant EXCEPT<\/a><div class=\"wp-block-latest-posts__post-full-content\"><!-- wp:paragraph -->\n<p>EXCEPT en SQL permet d&rsquo;extraire les diff\u00e9rences entre 2 tables.<br><br>Exemple :  2 tables article avec des contenus diff\u00e9rents<\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:image {\"id\":358,\"sizeSlug\":\"full\",\"linkDestination\":\"none\"} -->\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"346\" height=\"163\" src=\"https:\/\/fhix.fr\/wp-content\/uploads\/2025\/10\/image.png\" alt=\"\" class=\"wp-image-358\" srcset=\"https:\/\/fhix.fr\/wp-content\/uploads\/2025\/10\/image.png 346w, https:\/\/fhix.fr\/wp-content\/uploads\/2025\/10\/image-300x141.png 300w\" sizes=\"auto, (max-width: 346px) 100vw, 346px\" \/><\/figure>\n<!-- \/wp:image -->\n\n<!-- wp:paragraph -->\n<p><br>1) Extraire les lignes de ARTICLE qui ne sont pas dans ARTICLE2 :<br><code><strong>SELECT<\/strong> * <strong>FROM<\/strong> article<br><strong>EXCEPT<\/strong><br><strong>SELECT<\/strong> * <strong>FROM<\/strong> article2<\/code><br><mark style=\"background-color:var(--base-3)\" class=\"has-inline-color\">R\u00e9sultat :<\/mark><mark style=\"background-color:var(--base-3)\" class=\"has-inline-color has-accent-color\"><strong> Casquette jaune<\/strong><\/mark><\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:paragraph -->\n<p>2) Extraire les lignes de ARTICLE2 qui ne sont pas dans ARTICLE :<br><code><strong>SELECT<\/strong> * <strong>FROM<\/strong> article2<br><strong>EXCEPT<\/strong><br><strong>SELECT<\/strong> * <strong>FROM<\/strong> article<\/code><br><mark style=\"background-color:var(--base-3)\" class=\"has-inline-color\">R\u00e9sultat :<\/mark><mark style=\"background-color:var(--base-3)\" class=\"has-inline-color has-accent-color\"> <strong>Beret noir<\/strong><\/mark><\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:paragraph -->\n<p>3) Il est possible de comparer sur une ou plusieurs colonnes choisies.<br>Par exemple, extraire les couleurs de ARTICLE2 qui ne sont pas dans ARTICLE :<br><code><strong>SELECT<\/strong> couleur <strong>FROM<\/strong> article2<br><strong>EXCEPT<\/strong><br><strong>SELECT<\/strong> couleur <strong>FROM<\/strong> article<\/code><br><mark style=\"background-color:var(--base-3)\" class=\"has-inline-color\">R\u00e9sultat :<\/mark><mark style=\"background-color:var(--base-3)\" class=\"has-inline-color has-accent-color\"> <strong>noir<\/strong><\/mark><\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:paragraph -->\n<p><\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:paragraph -->\n<p>A noter, il est possible de comparer 2 fichiers source avec EXCEPT :<br>1- OVRDBF FILE(SOURCE1) TOFILE(BIB1\/QRPGSRC) MBR(PGM1)<br>2- OVRDBF FILE(SOURCE2) TOFILE(BIB2\/QRPGSRC) MBR(PGM2)<br>3- <strong>SELECT * FROM<\/strong> source1 <strong><mark class=\"has-inline-color has-accent-color\">EXCEPT<\/mark><\/strong> <strong>SELECT * FROM<\/strong> source2<\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:paragraph -->\n<p><br><\/p>\n<!-- \/wp:paragraph --><\/div><\/li>\n<li><a class=\"wp-block-latest-posts__post-title\" href=\"https:\/\/fhix.fr\/index.php\/2025\/09\/03\/agregation-de-donnees\/\">Agr\u00e9gation de donn\u00e9es avec LISTAGG<\/a><div class=\"wp-block-latest-posts__post-full-content\"><!-- wp:paragraph -->\n<p><code><strong>SELECT<\/strong> item ,<br><strong>LISTAGG<\/strong>(<strong>TRIM<\/strong>(couleur), ',') <strong>WITHIN GROUP<\/strong>(<strong>ORDER BY<\/strong> couleur)<br><strong>AS<\/strong> couleurs<br><strong>FROM<\/strong> article<br><strong>GROUP BY<\/strong> item<\/code><\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:paragraph -->\n<p><strong>Explications :<\/strong><br><strong>LISTAGG<\/strong> permet de lister dans une colonne les donn\u00e9es issues de plusieurs lignes.<br><code>LISTAGG(colonne_\u00e0_concat\u00e9ner , 's\u00e9parateur') WITHIN GROUP(ORDER BY colonne_de_tri)<\/code><br><br>Dans l&rsquo;exemple donn\u00e9, le SQL transforme le fichier article suivant<\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:image {\"id\":240,\"sizeSlug\":\"full\",\"linkDestination\":\"none\"} -->\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"439\" height=\"170\" src=\"https:\/\/fhix.fr\/wp-content\/uploads\/2025\/09\/image-6.png\" alt=\"\" class=\"wp-image-240\" srcset=\"https:\/\/fhix.fr\/wp-content\/uploads\/2025\/09\/image-6.png 439w, https:\/\/fhix.fr\/wp-content\/uploads\/2025\/09\/image-6-300x116.png 300w\" sizes=\"auto, (max-width: 439px) 100vw, 439px\" \/><\/figure>\n<!-- \/wp:image -->\n\n<!-- wp:paragraph -->\n<p>en 2 lignes compos\u00e9es de la colonne item et d&rsquo;une colonne de couleurs s\u00e9par\u00e9es par la virgule<\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:image {\"id\":241,\"sizeSlug\":\"full\",\"linkDestination\":\"none\"} -->\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"439\" height=\"68\" src=\"https:\/\/fhix.fr\/wp-content\/uploads\/2025\/09\/image-7.png\" alt=\"\" class=\"wp-image-241\" srcset=\"https:\/\/fhix.fr\/wp-content\/uploads\/2025\/09\/image-7.png 439w, https:\/\/fhix.fr\/wp-content\/uploads\/2025\/09\/image-7-300x46.png 300w\" sizes=\"auto, (max-width: 439px) 100vw, 439px\" \/><\/figure>\n<!-- \/wp:image -->\n\n<!-- wp:paragraph -->\n<p><\/p>\n<!-- \/wp:paragraph --><\/div><\/li>\n<li><a class=\"wp-block-latest-posts__post-title\" href=\"https:\/\/fhix.fr\/index.php\/2025\/08\/09\/supprimer-les-doublons-dans-un-fichier\/\">Supprimer les doublons dans un fichier<\/a><div class=\"wp-block-latest-posts__post-full-content\"><!-- wp:paragraph -->\n<p><code><strong>DELETE FROM<\/strong> monfichier a<br><strong>WHERE RRN<\/strong>(a) <strong>NOT IN<\/strong> ( <strong>SELECT MAX<\/strong>(<strong>RRN<\/strong>(b)) <strong>FROM<\/strong> monfichier b <br><strong>WHERE<\/strong> (a.cle1, a.cle2) = (b.cle1, b.cle2) )<\/code><\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:paragraph -->\n<p><strong>Explications :<\/strong><br>La fonction RRN permet de r\u00e9cup\u00e9rer le num\u00e9ro d&rsquo;enregistrement de la ligne. <br>Le SQL supprime donc les doublons (cl\u00e91+cl\u00e92 dans l&rsquo;exemple) qui ne correspondent pas au dernier num\u00e9ro d&rsquo;enregistrement.<\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:image {\"id\":163,\"sizeSlug\":\"full\",\"linkDestination\":\"none\"} -->\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"407\" height=\"146\" src=\"https:\/\/fhix.fr\/wp-content\/uploads\/2025\/08\/image-1.png\" alt=\"\" class=\"wp-image-163\" srcset=\"https:\/\/fhix.fr\/wp-content\/uploads\/2025\/08\/image-1.png 407w, https:\/\/fhix.fr\/wp-content\/uploads\/2025\/08\/image-1-300x108.png 300w\" sizes=\"auto, (max-width: 407px) 100vw, 407px\" \/><\/figure>\n<!-- \/wp:image -->\n\n<!-- wp:paragraph -->\n<p><\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:paragraph -->\n<p>Si on veut conserver le premier doublon et non le dernier doublon, il faut remplacer MAX par MIN :<br><code><strong>DELETE FROM<\/strong> monfichier a<br><strong>WHERE RRN<\/strong>(a) <strong>NOT IN<\/strong> ( <strong>SELECT MIN<\/strong>(<strong>RRN<\/strong>(b)) <strong>FROM<\/strong> monfichier b <br><strong>WHERE<\/strong> (a.cle1, a.cle2) = (b.cle1, b.cle2) )<\/code><\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:image {\"id\":164,\"sizeSlug\":\"full\",\"linkDestination\":\"none\"} -->\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"406\" height=\"145\" src=\"https:\/\/fhix.fr\/wp-content\/uploads\/2025\/08\/image-2.png\" alt=\"\" class=\"wp-image-164\" srcset=\"https:\/\/fhix.fr\/wp-content\/uploads\/2025\/08\/image-2.png 406w, https:\/\/fhix.fr\/wp-content\/uploads\/2025\/08\/image-2-300x107.png 300w\" sizes=\"auto, (max-width: 406px) 100vw, 406px\" \/><\/figure>\n<!-- \/wp:image -->\n\n<!-- wp:paragraph -->\n<p><\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:paragraph -->\n<p>Pour simplement chercher les doublons, on peut utiliser :<br><code><strong>SELECT<\/strong> *<strong> FROM<\/strong> monfichier a<br><strong>WHERE RRN<\/strong>(a) <strong>NOT IN<\/strong> ( <strong>SELECT <\/strong><strong>RRN<\/strong>(b) <strong>FROM<\/strong> monfichier b <br><strong>WHERE<\/strong> (a.cle1, a.cle2) = (b.cle1, b.cle2) )<\/code><\/p>\n<!-- \/wp:paragraph --><\/div><\/li>\n<\/ul>","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-372","page","type-page","status-publish"],"_links":{"self":[{"href":"https:\/\/fhix.fr\/index.php\/wp-json\/wp\/v2\/pages\/372","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fhix.fr\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/fhix.fr\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/fhix.fr\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fhix.fr\/index.php\/wp-json\/wp\/v2\/comments?post=372"}],"version-history":[{"count":2,"href":"https:\/\/fhix.fr\/index.php\/wp-json\/wp\/v2\/pages\/372\/revisions"}],"predecessor-version":[{"id":445,"href":"https:\/\/fhix.fr\/index.php\/wp-json\/wp\/v2\/pages\/372\/revisions\/445"}],"wp:attachment":[{"href":"https:\/\/fhix.fr\/index.php\/wp-json\/wp\/v2\/media?parent=372"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}