CTE Ricorsive: L’Eleganza della Semplicità in SQL – 1

Le Common Table Expressions (CTE) in SQL su IBM iSeries (AS/400) rappresentano uno strumento potente e flessibile per creare query temporanee e riutilizzabili, semplificando e migliorando la leggibilità delle query SQL complesse..

Di seguito gli articoli della serie CTE (incluso questo corrente):

• iSeries Sql – CTE Common Table Expressions – definizioni e utilizzo

• CTE Ricorsive: L’Eleganza della Semplicità in SQL – 1

• CTE Ricorsive: L’Eleganza della Semplicità in SQL – 2

• CTE Ricorsive: L’Eleganza della Semplicità in SQL – 3

• Recursive CTE come concatenare i risultati su un’unica riga

In particolare le Common Table Expressions (CTE) Ricorsive sono un potente strumento in SQL che permette di eseguire operazioni che, in assenza di questa funzionalità, richiederebbero lo sviluppo di programmi specifici o l’utilizzo di cicli complessi.

Queste CTE sono particolarmente utili per lavorare con dati gerarchici, come alberi o strutture ad albero, e per eseguire calcoli iterativi o ricorsivi.

In questo articolo vedremo un esempio significativo in dettaglio relativo alla “Navigazione in strutture gerarchiche”

Assegnazione livello gerarchico con CTE Ricorsive

Descrizione Contesto e Obiettivo

Questa query è progettata per visualizzare la struttura organizzativa di un’azienda in una forma gerarchica. Può essere utilizzata per comprendere le relazioni di report tra dipendenti e manager e per identificare la profondità della gerarchia organizzativa.

Mentre la tabella originale potrebbe contenere altre informazioni pertinenti, per lo scopo di questa query, l’attenzione è focalizzata esclusivamente su identificativo dipendente (ID), Nome dipendente (Nome) e identific<tivo Manager (IdManager).

L’obiettivo principale è generare un elenco in cui ad ogni dipendente (ID e Nome) è associato il livello gerarchico da 0 ad n, dove 0 rappresenta il top Manager o Azienda, 1 i dipendenti che dipendono dal top Manager, e così via.

Supponiamo di avere un archivio che rappresenta la seguente struttura gerarchica (dove il top Manager è I011):

il contenuto dell’archivio employee:

Query

WITH -- starting data
     employee (ID, Nome, IdManager) AS (
         VALUES  ('I011', 'Employee_011',  NULL) , ('I021', 'Employee_021', 'I011'),
                 ('I022', 'Employee_022', 'I011'), ('I023', 'Employee_023', 'I011'),
                 ('I031', 'Employee_031', 'I022'), ('I032', 'Employee_032', 'I022')
,
                 ('I041', 'Employee_041', 'I031'), ('I042', 'Employee_042', 'I031'),
                 ('I043', 'Employee_043', 'I031'), ('I045', 'Employee_045', 'I032'),
                 ('I046', 'Employee_046', 'I032'), ('I047', 'Employee_047', 'I032')
,
                 ('I051', 'Employee_051', 'I042'), ('I052', 'Employee_052', 'I046'),
                 ('I061', 'Employee_061', 'I051')
, ('I062', 'Employee_062', 'I051'),
                 ('I063', 'Employee_063', 'I051'), ('I064', 'Employee_064', 'I052')
               , ('I065', 'Employee_065', 'I052'), ('I066', 'Employee_066', 'I052'))
  ,  hierarchy (ID, Nome, Livello) AS (
         SELECT ID, Nome
              , 0 AS Livello
           FROM employee
          WHERE IdManager IS NULL -- Punto di partenza: manager di livello più alto
        UNION ALL
         SELECT e.ID, e.Nome, h.Livello + 1
           FROM employee e
          INNER JOIN hierarchy h ON e.IDManager = h.ID
          )
SELECT * FROM hierarchy;

Risultato

Tabella risultato (hierarchy)

Descrizione dettagliata della query

Definizione della Tabella dei Dipendenti (employee):

• La CTE employee viene definita come una tabella temporanea contenente tre colonne: ID (identificativo del dipendente), Nome (nome del dipendente) e IdManager (identificativo del manager di ciascun dipendente).
   
• La tabella è popolata con una serie di valori che rappresentano una struttura organizzativa aziendale. Ad esempio, ‘I011’ è un dipendente senza manager (NULL), il che implica che potrebbe essere un dirigente o un capo dipartimento (top Manager).

Costruzione della Gerarchia (hierarchy):

• La CTE hierarchy è una CTE ricorsiva composta da due parti: una parte base e una parte ricorsiva, separate dall’operatore UNION ALL.
   
• Parte Base: Seleziona i dipendenti che non hanno un manager (IdManager IS NULL). Questi dipendenti rappresentano il punto di partenza della gerarchia (ad esempio, la direzione aziendale). A questi viene assegnato un Livello 0, indicando il livello più alto della gerarchia.
   
• Parte Ricorsiva: Collega ogni dipendente al proprio manager attraverso un INNER JOIN tra la CTE employee e la stessa hierarchy. Ad ogni passaggio della ricorsione, il livello aumenta di 1 (h.Livello + 1), rappresentando così la posizione di ciascun dipendente nella gerarchia aziendale.

Selezione Finale:

• La query finale SELECT * FROM hierarchy; seleziona tutti i record dalla CTE hierarchy, risultando in un elenco di dipendenti con i rispettivi livelli nella struttura organizzativa.

Considerazioni

• La CTE ricorsiva è un modo efficace per gestire dati gerarchici o ricorsivi in SQL, specialmente quando la struttura dei dati non è piatta ma nidificata o multi-livello.
   
• Questo tipo di query può essere particolarmente utile in ambienti aziendali dove la struttura organizzativa è complessa.
   
• È importante notare che per evitare loop infiniti, la base della ricorsione (parte base della CTE) deve essere ben definita. In questo caso, si parte dai dipendenti senza un manager.

Tenere presente che l’implementazione e le funzionalità effettive potrebbero variare a seconda della versione del sistema operativo iSeries e del database DB2. È sempre buona norma fare riferimento alla documentazione ufficiale IBM per avere informazioni più precise e dettagliate