Oggi partendo da una procedura commissionatami che doveva fare un aggiornamento dei dati sono arrivato ad approfondire un operatore che forse ho visto di sfuggita in passato ma certamente non ho usato ne’ capito, ragion per cui oggi, avendo un attimo di tempo ho deciso di dedicarmici.
Il punto di partenza è una organizzazione dei dati di cui ignoro le motivazioni alla base ma che mi sembra, in un database relazionale, poco corretta; si tratta infatti di un campo di tipo VARCHAR2 che contiene degli id numerici che fanno riferimento ad un campo di un’altra tabella separati da una virgola. Concedo il beneficio del dubbio ma non ho capito perché non si è usato una tabella di relazione come in un database relazionale si dovrebbe fare. Il fatto è che a livello di SQL fare anche una banale join per estrapolare i dati diventa una cosa assai complicata. Per cercare di spiegarmi cerco di riportare un caso di test:
create table T1 (
t1_id number,
t1_desc varchar2(30)
);
create table T2 (
t2_id number,
t2_t1ids varchar2(90),
t2_desc varchar2(30)
);
insert into T1 values (1,'a');
insert into T1 values (2,'b');
insert into T1 values (3,'c');
insert into T1 values (4,'d');
insert into T2 values (1,'1,2','uno');
insert into T2 values (2,'2,3,4','due');
insert into T2 values (3,'1,4','tre');
insert into T2 values (4,'4','quattro');
Ora sul campo T2.T2_T1IDS ho degli id separati da virgola in un campo di tipo stringa che dovrebbero fare riferimento al campo T1.T1_ID. In un database relazionale ci sarebbe una tabella T3 che come struttura avrebbe due campi, uno che punta a T1.T1_ID e uno che punta a T2.T2_ID, se poi voglio estrarre i dati li ricostruisco con una join sulle tre tabelle; qui non è così facile, magari a me sfugge qualcosa ma di sicuro se scrivo questo non va bene:
SVILUPPO@svil112 > select * from t2 join t1 on (t1_id in t2_t1ids);
select * from t2 join t1 on (t1_id in t2_t1ids)
*
ERROR at line 1:
ORA-01722: invalid number
Io sono giunto alla conclusione che bisogna in qualche modo fare un parsing del campo T2.T2_T1IDS. In passato ho fatto delle procedurine per fare una cosa simile ma siccome non mi piacevano molto e mi sembravano più complesse del necessario ho pensato di fare una nuova ricerca su internet, in pratica ho cercato una funzione inversa rispetto a LISTAGG e sono arrivato a questa discussione su ORAFAQ. Qui ho trovato due interessanti soluzioni che ho deciso di approfondire e capire e questo è il motivo di questo post. La prima, applicata al mio caso di test dovrebbe essere più o meno così:
SVILUPPO@svil112 > with test as
2 (select * from t2 where t2_desc='due' )
3 select * from
4 (select t2_id,regexp_substr(t2_t1ids, '[^,]+', 1, level) t1id
5 from test
6 connect by level <= length(regexp_replace(t2_t1ids, '[^,]+')) + 1)
7 join t1 on t1_id=t1id;
T2_ID T1ID T1_ID T1_DESC
---------- ---- ---------- ------------------------------
2 2 2 b
2 3 3 c
2 4 4 d
Da quello che ho capito io la funzione regexp_substr(t2_t1ids, ‘[^,]+’, 1, level) prende una occorrenza di tutto ciò che non contiene al suo interno una virgola, “level” indica quale occorrenza, quindi la sottoquery restituisce un record per ogni id nel campo t2_t1ids. Nella discussione viene fatto un intervento che spiega come quella prima soluzione non funziona nel caso di più record estratti dalla query nella sezione “WITH”, infatti:
SVILUPPO@svil112 > with test as
2 (select * from t2 where t2_desc='due' or t2_desc='tre' )
3 select * from
4 (select t2_id,regexp_substr(t2_t1ids, '[^,]+', 1, level) t1id
5 from test
6 connect by level <= length(regexp_replace(t2_t1ids, '[^,]+')) + 1)
7 join t1 on t1_id=t1id;
T2_ID T1ID T1_ID T1_DESC
---------- ---- ---------- ------------------------------
3 1 1 a
2 2 2 b
2 3 3 c
2 3 3 c
2 4 4 d
3 4 4 d
2 4 4 d
2 4 4 d
3 4 4 d
2 4 4 d
La “connect by level<….” provoca una moltiplicazione dei record nel risultato che non va bene. Anche qui avrei da approfondire perché confesso che non ho chiarissimi alcuni dettagli del funzionamento delle query gerarchiche. Se non ho capito male al primo livello ci sono tutti i valori della tabella diciamo n, al secondo livello per ogni valore ci sono n figli e così via; qui la cosa si complica e richiederà altri approfondimenti.
La variante suggerita per risolvere il problema nel caso più generico è un po’ più complessa ed applicata al mio caso di test dovrebbe essere così:
SVILUPPO@svil112 > WITH test AS
2 (SELECT * FROM t2 WHERE t2_desc='due' or t2_desc='tre' )
3 SELECT * FROM
4 (SELECT t2_id,REGEXP_SUBSTR(t2_t1ids, '[^,]+', 1, b.column_value) t1id
5 FROM test a CROSS JOIN
6 TABLE
7 (
8 CAST
9 (
10 MULTISET
11 (
12 SELECT LEVEL FROM DUAL
13 CONNECT BY LEVEL <= REGEXP_COUNT(a.t2_t1ids, '[^,]+')
14 )
15 AS SYS.odciNumberList
16 )
17 ) b
18 )
19 join t1 on t1_id=t1id;
T2_ID T1ID T1_ID T1_DESC
---------- ---- ---------- ------------------------------
2 2 2 b
2 3 3 c
2 4 4 d
3 1 1 a
3 4 4 d
Così mi pare corretta, seppur un po’ lunga.
Nell’ultima soluzione proposta ci sono alcune cose che io non conoscevo molto bene, la prima è l’utilizzo della sintassi CROSS JOIN che serve a generare un prodotto cartesiano con la sintassi ANSI della JOIN. La seconda cosa è quello che sembra un operatore MULTISET, in realtà è parte dell’operatore CAST. Come dice la documentazione MULTISET informa Oracle di prendere il risultato della sottoquery e restituire un valore “collection”. Questa “collection” viene convertita nel tipo predefinito SYS.odciNumberList che è documentato qui. A suo volta questo tipo può essere dato in pasto all’operatore TABLE e così di fatto la lista di valori è trasformata al volo in una tabella.
Non credo e spero di dover ritornare su situazioni analoghe a questa, in ogni caso quando capiterà avrò pronto qui qualche appunto che mi aiuterà.
Riferimenti:
Tipi predefiniti (Database Data Cartridge Developer’s Guide 11.2) , per 12.1 qui.
OraFaq
P.S.
2017/05/04: credo valga la pena di riportare qui un altro post interessante che ho trovato oggi: https://stewashton.wordpress.com/2016/06/22/new-improved-in-lists/
Oracle and other 