Le Common Table Expressions (CTE) in SQL su IBM iSeries (AS\/400), come in altri sistemi di gestione di database, sono una caratteristica potente e flessibile che permette di creare query temporanee e riutilizzabili all’interno di un’istruzione SQL pi\u00f9 ampia.<\/p>\n\n\n\n
Sono particolarmente utili per strutturare e semplificare query complesse, consentendo una maggiore leggibilit\u00e0 e manutenibilit\u00e0 del codice SQL.<\/p>\n\n\n\n
Di seguito gli articoli della serie CTE (incluso questo corrente):<\/p>\n\n\n\n
<\/p>\n\n\n\n
Descrizione Tecnica delle CTE<\/strong><\/p>\n\n\n\n Considerazioni per IBM iSeries<\/strong><\/p>\n\n\n\n vediamo alcuni esempi di utilizzo:<\/p>\n\n\n\n CTE Common Table Expression per assegnare numerazione over \u201cpartition by\u201d<\/strong><\/p>\n\n\n\n Descrizione Contesto e Obiettivo<\/strong><\/p>\n\n\n\n La query in questione \u00e8 progettata per operare su una tabella che registra le transazioni, identificate da un ID di transazione (TRANS_ID<\/strong>) e associate a una specifica data di movimento (DATE_MOV<\/strong>).<\/p>\n\n\n\n Mentre la tabella originale potrebbe contenere altre informazioni pertinenti, per lo scopo di questa query, l’attenzione \u00e8 focalizzata esclusivamente su TRANS_ID<\/strong> e DATE_MOV<\/strong>.<\/p>\n\n\n\n L’obiettivo principale \u00e8 generare un elenco in cui ogni transazione \u00e8 elencata con un numero progressivo unico e la relativa data di movimento.<\/p>\n\n\n\n Questo numero progressivo inizia da 1 per ciascuna data di movimento e aumenta di uno per ogni transazione successiva che si verifica nella stessa data.<\/p>\n\n\n\n In altre parole, la query assegna un numero sequenziale alle transazioni, riavviando il conteggio da 1 per ogni nuova data di movimento, garantendo cos\u00ec che ciascuna transazione riceva un identificatore univoco all’interno del suo specifico giorno.<\/p>\n\n\n\n Query<\/strong><\/p>\n\n\n\n Risultato<\/strong><\/p>\n\n\n\n Tabella originale (W1)<\/p>\n<\/div>\n\n\n\n Tabella risultato (W2)<\/p>\n<\/div>\n<\/div>\n\n\n\n Descrizione dettagliata della query<\/strong><\/p>\n\n\n\n La query SQL utilizza Common Table Expressions per creare un set di dati di esempio e poi assegnare un numero progressivo a ciascuna riga all’interno di gruppi basati sulla data del movimento (DATE_MOV<\/strong>), ordinando per l’ID della transazione (TRANS_ID<\/strong>).<\/p>\n\n\n\n La query \u00e8 divisa in due parti principali:<\/p>\n\n\n\n W1: Definizione dei Dati di Partenza<\/strong><\/p>\n\n\n\n La prima CTE, W1<\/strong>, definisce un set di dati di partenza con due colonne: TRANS_ID<\/strong> e DATE_MOV<\/strong>. Vengono inseriti manualmente dei valori per simulare i dati reali.<\/p>\n\n\n\n W2: Assegnazione del Numero Progressivo<\/strong><\/p>\n\n\n\n La seconda CTE, W2<\/strong>, si basa sui dati definiti in W1<\/strong>. Utilizza la funzione ROW_NUMBER()<\/strong> per assegnare un numero progressivo a ciascuna transazione (TRANS_ID<\/strong>) all’interno di ogni data (DATE_MOV<\/strong>). La funzione ROW_NUMBER()<\/strong> \u00e8 partizionata per DATE_MOV<\/strong> per garantire che la numerazione ricominci da 1 per ciascuna nuova data. Inoltre, l’ordine \u00e8 stabilito dall’TRANS_ID<\/strong> per determinare l’ordine di assegnazione dei numeri all’interno di ogni gruppo di date.<\/p>\n\n\n\n Analizziamo pi\u00f9 in dettaglio questa parte:<\/p>\n\n\n\n Questa espressione \u00e8 un esempio di una funzione di finestra in SQL, nello specifico ROW_NUMBER()<\/strong>, che viene utilizzata per assegnare un numero progressivo unico a ogni riga all’interno di un set di risultati di una query. Andiamo a esaminare ogni componente di questa espressione:<\/p>\n\n\n\n ROW_ ROW_NUMBER() OVER (PARTITION BY DATE_MOV ORDER BY W1.TRANS_ID)<\/strong> AS ROW_NUM<\/strong> Tenere presente che l\u2019implementazione e le funzionalit\u00e0 effettive potrebbero variare a seconda della versione del sistema operativo iSeries e del database DB2. \u00c8 sempre buona norma fare riferimento alla documentazione ufficiale IBM per avere informazioni pi\u00f9 precise e dettagliate<\/p>\n\n\n\n <\/p>\n\n\n\n CTE Common Table Expression per individuare interruzioni nella sequenza numerica<\/strong><\/p>\n\n\n\n Descrizione Contesto e Obiettivo<\/strong><\/p>\n\n\n\n La query in questione \u00e8 progettata per operare su una tabella che registra le transazioni, identificate da un Codice Movimento (MovCode<\/strong>) e associate a uno specifico identificativo (NumberID<\/strong>).<\/p>\n\n\n\n Mentre la tabella originale potrebbe contenere altre informazioni pertinenti, per lo scopo di questa query, l’attenzione \u00e8 focalizzata esclusivamente su MovCode<\/strong> e NumberID<\/strong>.<\/p>\n\n\n\n L’obiettivo principale \u00e8 generare un elenco in cui identificare e contrassegnare eventuali interruzioni nella sequenza numerica dei numeri identificativi (NumberID<\/strong>) per ogni Codice Movimento (MovCode<\/strong>).<\/p>\n\n\n\n Query<\/strong><\/p>\n\n\n\n Risultato<\/strong><\/p>\n\n\n\n Tabella originale ( XX )<\/p>\n<\/div>\n\n\n\n Tabella risultato (CTE_CheckRegister)<\/p>\n<\/div>\n<\/div>\n\n\n\n Descrizione dettagliata della query<\/strong><\/p>\n\n\n\n Questa query SQL utilizza le CTE (Common Table Expressions) e la funzione LEAD<\/strong> per identificare e contrassegnare eventuali interruzioni nella sequenza numerica dei numeri identificativi (NumberID) per ogni Codice Movimento (MovCode).<\/p>\n\n\n\n Ecco una spiegazione dettagliata di ciascuna parte della query:<\/p>\n\n\n\n Tenere presente che l\u2019implementazione e le funzionalit\u00e0 effettive potrebbero variare a seconda della versione del sistema operativo iSeries e del database DB2. \u00c8 sempre buona norma fare riferimento alla documentazione ufficiale IBM per avere informazioni pi\u00f9 precise e dettagliate<\/p>\n","protected":false},"excerpt":{"rendered":" Le Common Table Expressions (CTE) in SQL su IBM iSeries (AS\/400), come in altri sistemi di gestione di database, sono una caratteristica potente e flessibile che permette di creare query temporanee e riutilizzabili all’interno di un’istruzione SQL pi\u00f9 ampia. Sono…<\/p>\n","protected":false},"author":3,"featured_media":4078,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"_kadence_starter_templates_imported_post":false,"_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"_kad_post_classname":"","footnotes":""},"categories":[15,17],"tags":[46,124,125,126,127],"class_list":["post-4069","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-gestire-as400-con-sql","category-piu-in-profondita","tag-sql","tag-cte-common-table-expression","tag-row_number-over","tag-lead-over","tag-window-function"],"_links":{"self":[{"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/posts\/4069","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/comments?post=4069"}],"version-history":[{"count":16,"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/posts\/4069\/revisions"}],"predecessor-version":[{"id":4372,"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/posts\/4069\/revisions\/4372"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/media\/4078"}],"wp:attachment":[{"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/media?parent=4069"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/categories?post=4069"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/tags?post=4069"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\n
<\/li>\n\n\n\nWITH NomeCTE (Colonna1, Colonna2, ...) AS ( SELECT ... FROM ... WHERE ... ) SELECT * FROM NomeCTE;<\/pre><\/li>\n\n\n\n
<\/li>\n\n\n\n
<\/li>\n\n\n\n
<\/li>\n\n\n\n
<\/li>\n\n\n\n\n
<\/li>\n\n\n\n\n
WITH -- starting data\n W1 (TRANS_ID, DATE_MOV) AS (\n VALUES ('T0011', '2024-01-10'), ('T0012', '2024-01-10'), ('T0013', '2024-01-10')\n , ('T0014', '2024-01-10'), ('T0015', '2024-01-10'), ('T0016', '2024-01-10')\n , ('T0017', '2024-01-10'), ('T0018', '2024-01-10'), ('T0019', '2024-01-10')\n , ('T0020', '2024-01-10'), ('T0023', '2024-01-10'), ('T0024', '2024-01-10')\n , ('T0025', '2024-01-10'), ('T0026', '2024-01-10'), ('T0127', '2024-01-10')\n , ('T0128', '2024-01-10'), ('T0129', '2024-01-10'), ('T0149', '2024-01-10')\n , ('T0150', '2024-01-11'), ('T0151', '2024-01-11'), ('T0152', '2024-01-11')\n , ('T0165', '2024-01-11'), ('T0166', '2024-01-11'), ('T0167', '2024-01-11')\n , ('T0168', '2024-01-11'), ('T0201', '2024-01-11'))\n -- assigns progressive numbering to the same DATA_MOV, ordering by TRANS_ID\n , W2 (TRANS_ID, ROW_NUM, DATE_MOV)\n as (select trim(W1.TRANS_ID)\n , row_number() over ( partition by DATE_MOV -- 'COMMON_VALUE'\n order by W1.TRANS_ID) ROW_NUM\n , W1.DATE_MOV\n from W1 order by W1.TRANS_ID)\nselect * from W2;\n<\/pre>\n\n\n\nTRANS_ID DATE_MOV\nT0011 2024-01-10\nT0012 2024-01-10\nT0013 2024-01-10\nT0014 2024-01-10\nT0015 2024-01-10\nT0016 2024-01-10\nT0017 2024-01-10\nT0018 2024-01-10\nT0019 2024-01-10\nT0020 2024-01-10\nT0023 2024-01-10\nT0024 2024-01-10\nT0025 2024-01-10\nT0026 2024-01-10\nT0127 2024-01-10\nT0128 2024-01-10\nT0129 2024-01-10\nT0149 2024-01-10\nT0150 2024-01-11\nT0151 2024-01-11\nT0152 2024-01-11\nT0165 2024-01-11\nT0166 2024-01-11\nT0167 2024-01-11\nT0168 2024-01-11\nT0201 2024-01-11<\/pre>\n<\/div>\n\n\n\n
TRANS_ID ROW_NUM DATE_MOV\nT0011 1 2024-01-10\nT0012 2 2024-01-10\nT0013 3 2024-01-10\nT0014 4 2024-01-10\nT0015 5 2024-01-10\nT0016 6 2024-01-10\nT0017 7 2024-01-10\nT0018 8 2024-01-10\nT0019 9 2024-01-10\nT0020 10 2024-01-10\nT0023 11 2024-01-10\nT0024 12 2024-01-10\nT0025 13 2024-01-10\nT0026 14 2024-01-10\nT0127 15 2024-01-10\nT0128 16 2024-01-10\nT0129 17 2024-01-10\nT0149 18 2024-01-10\nT0150 1 2024-01-11\nT0151 2 2024-01-11\nT0152 3 2024-01-11\nT0165 4 2024-01-11\nT0166 5 2024-01-11\nT0167 6 2024-01-11\nT0168 7 2024-01-11\nT0201 8 2024-01-11\n<\/pre>\n<\/div>\n<\/div>\n\n\n\n
ROW_NUMBER() OVER (PARTITION BY DATE_MOV ORDER BY W1.TRANS_ID) AS ROW_NUM,<\/pre>\n\n\n\n
<\/strong> \u00e8 una funzione di finestra (window functions) che assegna un numero sequenziale a ogni riga in un set di risultati. Il numero inizia da 1 per la prima riga e aumenta di 1 per ogni riga successiva.<\/p>\n\n\n\n
La clausola OVER<\/strong> specifica come la funzione di finestra ROW_NUMBER()<\/strong> deve essere applicata al set di risultati.<\/p>\n\n\n\n\n
Infine, AS ROW_NUM<\/strong> assegna un nome alla colonna che contiene il numero progressivo generato dalla funzione ROW_NUMBER()<\/strong>. In questo caso, la colonna sar\u00e0 denominata ROW_NUM<\/strong>.<\/p>\n\n\n\n
\n\n\n\nWITH xx (MovCode, NumberID)\n AS (VALUES ('A1002','101')\n ,('A1002','102')\n ,('A1002','104')\n ,('A1002','105')\n ,('A1002','108')\n ,('A1002','109')\n ,('A1002','110')\n ,('B2002','201')\n ,('B2002','202')\n ,('B2002','203')\n ,('B2002','204')\n ,('B2002','210')\n ,('B2002','211')\n ,('B2002','214'))\n , CTE_CheckRegister (MovCode, NumberID, NextNumberID)\n AS (\n SELECT MovCode,\n NumberID,\n Lead(NumberID, 1) OVER (PARTITION BY MovCode ORDER BY NumberID) NextNumberID\n FROM xx\n )\nSELECT MovCode,\n CASE\n WHEN NextNumberID is NULL THEN NumberID\n WHEN (NumberID = NextNumberID - 1) THEN NumberID\n ELSE NumberID concat ' ***'\n END as NumberID\nFROM CTE_CheckRegister\nORDER BY MovCode, NumberID;<\/pre>\n\n\n\nMOVCODE\tNUMBERID\nA1002\t101\nA1002\t102\nA1002\t104\nA1002\t105\nA1002\t108\nA1002\t109\nA1002\t110\nB2002\t201\nB2002\t202\nB2002\t203\nB2002\t204\nB2002\t210\nB2002\t211\nB2002\t214<\/pre>\n<\/div>\n\n\n\n
MOVCODE\tNUMBERID\nA1002\t101\nA1002\t102 ***\nA1002\t104\nA1002\t105 ***\nA1002\t108\nA1002\t109\nA1002\t110\nB2002\t201\nB2002\t202\nB2002\t203\nB2002\t204 ***\nB2002\t210\nB2002\t211 ***\nB2002\t214<\/pre>\n<\/div>\n<\/div>\n\n\n\n
\n
\n
\n
\n
\n
\n
\n
\n