découpage de caractère (suite)

Version imprimable

Bonjour,

Je déplace mon dernier post qui situé dans un thread "resolu", n'a surement pas la visibilité nécessaire, en espérant que les modérateurs ne m'en veuillent pas.

C'est donc la suite corrigée et commentée de : http://www.developpez.net/forums/showthread.php?t=65198

---------------------------------------------------------

J'ai une table 'table' avec deux champs 'id' et 'num' qui contient des chaines du type :
Code:

1 2 3 4 5 create table test (id number, num varchar2(255)); insert into test values (1,'12,12345, A345, TOTO'); insert into test values (2,'454'); insert into test values (3,'13,454');
Je veux comme résultat final dans une autre table 'result' avec comme pour sequence, l'ordre d'afichage des valeurs de num pour chaque id
Code:

1 2 3 4 5 6 7 8 9 id num sequence 1 12 1 1 12345 2 1 A345 3 1 TOTO 4 2 454 1 3 13 1 3 454 2
En lisant le thread d'origine, je suis arrivé à des premiers résultats intéressants avec la requête suivante :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Drop table result; Create table result (id number , num varchar2(50), seq number); insert into result (id, num) select id, trim(substr(num, instr(num,',',1,rr.r)+1, instr(num,',',1,rr.r+1)-instr(num,',',1,rr.r)-1 )) AS num from (select id, ','||num||',' AS num, length(num)-length(replace(num,',',''))+1 AS cnt from test ) test, (select rownum r from all_objects) rr where rr.r <= test.cnt ; select id, num from result;
1 - est ce possible de la simplifier ?
2 - l'ordre d'insertion n'est pas le bon non plus mais quand le champ séquence sera rempli cela ne posera pas de problème
3 - comment incrémenter/remplir le champ séquence ?
Est-ce jouable en SQL seul ou faut-il du PL/SQL ?

La deuxième partie du pb consiste à ne pas inserer certaines valeurs. Par exemple comment créer une liste de valeur qui ne doivent pas être inséré dans la table : par exemple si je ne veux pas intégrer les valeurs 12 et 13 voici ce que je voudrais obtenir :
Code:

1 2 3 4 5 6 7 id, num, sequence 1 12345 1 1 A345 2 1 TOTO 3 2 454 1 3 454 1
Il ya une troisième partie mais je vais m'arrêter ici.

Merci pour votre aide

31/05/2006, 08h40
Yorglaa

bonjour,
pour ce qui est de la séquence, ça doit le faire avec une fonction analytique...

dans le Select, ajoute cette 3ème colonne (bien entendu il faut aussi déclarer la colonne Seq dans l'insert) :

Code:

, Row_Number() over (Partition by id Order by Num) as seq
31/05/2006, 11h38
sygale

Citation:

Envoyé par Yorglaa

Code:

, Row_Number() over (Partition by id Order by Num) as seq

Si j'ai bien compris l'article de Laly KATTOOR

Il ne faut pas utilisé le Dense_rank plustôt ?

Code:

DENSE_RANK() over (Partition by id Order by Num) as seq
31/05/2006, 11h52
Yorglaa

ben ça dépend de la façon dont tu veux calculer ta "séquence"...
mais notre ami tuyau45 a été un peu vague là-dessus...

je suppose qu'il devra essayer les 2 méthode pour voir laquelle lui convient !

mais à priori ça donne le même résultat !

TEST :

Code:

1
2
3
4
5
6
7
8
9
10
create table test (id number, num varchar2(10)) ;
insert into test values (1, 'AA' ) ;
insert into test values (1, 'BB' ) ;
insert into test values (1, 'CA' ) ;
insert into test values (1, 'CB' ) ;
insert into test values (2, 'AA' ) ;
insert into test values (3, 'AA' ) ;
insert into test values (3, 'AA1' ) ;
insert into test values (3, 'AAB' ) ;
Commit ;

Select avec Row_Number :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select 
        id
        , num
        , Row_Number() over (Partition by id Order by num) as seq
from test ;
 
ID	NUM	SEQ
 
1	AA	1
1	BB	2
1	CA	3
1	CB	4
2	AA	1
3	AA	1
3	AA1	2
3	AAB	3

Select avec Dense_Rank :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select 
        id
        , num
        , Dense_Rank() over (Partition by id Order by num) as seq
from test ;
 
 
ID	NUM	SEQ
 
1	AA	1
1	BB	2
1	CA	3
1	CB	4
2	AA	1
3	AA	1
3	AA1	2
3	AAB	3

Vous êtes bien compliqués ;) Faut juste récupérer la colonne rr.r

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT ID, trim(SUBSTR(num, INSTR(num,',',1,rr.r) + 1, 
		INSTR(num,',',1,rr.r+1)-INSTR(num,',',1,rr.r)-1 )) AS numdec,
			num, r
FROM (
	SELECT ID, ','||num||',' AS num, 
			LENGTH(num)-LENGTH(REPLACE(num,',',''))+1 AS cnt 
		FROM MCTEST 
) MCTEST,
	(SELECT ROWNUM r 
	FROM all_objects
	WHERE ROWNUM < (SELECT MAX(LENGTH(num)) FROM mctest)) rr 
WHERE rr.r <= MCTEST.cnt
ORDER BY 1, 4

Sinon pour limiter ..
En rajoutant

Code:

1
2
AND trim(SUBSTR(num, INSTR(num,',',1,rr.r) + 1, 
		INSTR(num,',',1,rr.r+1)-INSTR(num,',',1,rr.r)-1 )) NOT IN ('12', '13')

dans la clause where principale, tu ne les a plus.. mais il faut alors utiliser les fonctions analytiques

Désolé pour cette réponse un peu tardive, mais certains problèmes de gestion du forum ces derniers jours m'ont enpeché de répondre plus vite.

Bref, merci beaucoup pour vos conseils, cela m'a permis d'avancer à grand pas (pour moi ;) ) ... J'ai cependant encore rencontré qqs problèmes et j'ai besoin de vos lumières ...

Dans mon cas le DENSE_RANK() ne fonctionne pas correctement (ou je n'ai pas su l'utiliser correctement), c'est donc avec Row_Number() que je génère ma séquence.

1 - la requête de McM marche très bien dans le cas ou il n'y a pas de NOT IN pour retirer les valeurs non voulues :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
Drop table result;
Create table result (id varchar2(10) , numdec varchar2(50),num varchar2(50), r number);
insert into result (id, numdec, r)
select 
	id,
	trim(substr(num, instr(num,',',1,rr.r)+1, instr(num,',',1,rr.r+1)-instr(num,',',1,rr.r)-1 )) AS numdec,
	r
from
	(select 
		id,
		','||num||',' AS num,
		length(num)-length(replace(num,',',''))+1 AS cnt
	from test ) test,
	(select 
		rownum r 
	from all_objects
	where rownum < (
		select 
			max(length(num)) 
		from test)
	) rr
where 
	rr.r <= test.cnt
order by 1,3;
select id, numdec, r from result;

résultat

Code:

1
2
3
4
5
6
7
8
9
 
ID  NUMDEC       R
1   12           1
1   12345        2
1   A345         3
1   TOTO         4
2   454          1
3   13           1
3   454          2

2 - si j'ajoute le NOT IN ('12','13') + une fonction analytique

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

Drop table result;
Create table result (id varchar2(10) , numdec varchar2(50),num varchar2(50), seq number);
insert into result (id, numdec, seq)
select 
	id,
	trim(substr(num, instr(num,',',1,rr.seq)+1, instr(num,',',1,rr.seq+1)-instr(num,',',1,rr.seq)-1 )) AS numdec,
	Row_number() over (Partition by id Order by Num) as seq
from
	(select 
		id,
		','||num||',' AS num,
		length(num)-length(replace(num,',',''))+1 AS cnt
	from test ) test,
	(select 
		rownum seq 
	from all_objects
	where rownum < (
		select 
			max(length(num)) 
		from test)
	) rr
where 
	rr.seq <= test.cnt
	AND trim(substr(num, instr(num,',',1,rr.seq)+1, instr(num,',',1,rr.seq+1)-instr(num,',',1,rr.seq)-1 )) not in ('12','13')
order by 1,3;
select id, numdec, seq from result;

Résultat

Code:

1
2
3
4
5
6
7
 
ID  NUMDEC       SEQ
1   TOTO         1
1   A345         2
1   12345        3
2   454          1
3   454          1

pas bon car je veux que l'ordre des occurences dans la chaine soit conservé, soit :

Code:

1
2
3
4
5
6
7
 
ID  NUMDEC       SEQ
1   12345        1
1   A345         2
1   TOTO         3
2   454          1
3   454          1

3 - Après avoir testé veinement plusieurs solutions et en désespoir de cause j'ai trouvé une autre méthode, que je trouve plus simple, qui utilise une fonction.

la fonction

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
create or replace type myTable as table of varchar2(255);
/
create or replace function str2tbl( p_str in varchar2 ) 
return myTable
pipelined
as
	l_str   long default p_str || ',';
	l_n        number;
begin
	loop
		l_n := instr( l_str, ',' );
		exit when (nvl(l_n,0) = 0);
		pipe row(trim(substr(l_str,1,l_n-1)));
		l_str := ltrim( substr( l_str, l_n+1 ) );
	end loop;
	return;
end;
/

la requête

Code:

1
2
3
4
5
6
7
8
9
10
11
 
select
	test.id,
	t2.column_value,
	Row_number() over (Partition by id Order by Num) as seq
from 
	test,
	TABLE(str2tbl(test.num)) t2
where
	t2.column_value not in ('12','13')
;

Résultat

Code:

1
2
3
4
5
6
7
 
ID  column_value  seq
1   12345        1
1   A345         2
1   TOTO         3
2   454          1
3   454          1

Superbe !

Mais, car il y a un mais en testant sur un autre jeu de données j'ai un problème sur l'ordre des occurences.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
drop table test2;
create table test2 (id int, num varchar2(250));
insert into test2 values ('1','a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t);
select
   test2.id,
   t2.column_value,
   Row_number() over (Partition by id Order by Num) as seq
from
   test2,
   TABLE(str2tbl(test2.num)) t2
;

Résultat

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
ID    COLUMN_VALUE    SEQ
1     a               1
1     t               2
1     c               3
1     d               4
1     e               5
1     f               6
1     g               7
1     h               8
1     i               9
1     j               10
1     k               11
1     l               12
1     m               13
1     n               14
1     o               15
1     p               16
1     q               17
1     r               18
1     s               19
1     b               20

le 't' et le 'b' sont bizarement positionnés ... Savez-vous pourquoi ? et comment régler le problème ?

Désole pour ce post aussi long, et encore merci pour votre patience

OK...
alors en restant avec la fonction analytique Row_Number, je vois 2 choses...

la première est de faire l'order by DE LA FONCTION ANALYTIQUE avec ta même fonction de découpage de la chaîne...
Code:

1 2 3 4 5 Row_number() over (Partition by id Order by trim( substr(num, instr(num,',',1,rr.seq)+1 , instr(num,',',1,rr.seq+1)-instr(num,',',1,rr.seq)-1 ) ) ) as seq
la deuxière est que dans ton select final (select id, numdec, seq from result) il n'y a pas d'order by... alors tu n'as AUCUNE garantie que Oracle te ramènera les Rows toujours dans le même ordre...

alors, est-il judicieux de calculer la position de la ligne (avec Row_Number) au moment de l'insert ?
ou une autre solution serait de passer outre cette étape au moment de l'insert mais de l'inclure au moment du Select final... du coup comme ta chaîne est déjà découpée, tu peux faire ton Order by (toujours celui de la fonction analytique) sur la chaîne DEJA découpée...

02/06/2006, 18h10
fpouget
Merci pour cette réponse Yorglaa, mais cela ne réponds pas à ma question, en effet je ne veux surtout pas trier les occurences de la chaine, mais les insérer dans leur ordre d'apparition.

pour illustrer : si j'ai dans ma table test j'ai la chaine suivante :

Code:

5, 4, 2, 1, 3

dans ma table result j'aurai:
Code:

1 2 3 4 5 6 num seq 5 1 4 2 2 3 1 4 3 5
02/06/2006, 18h23
Yorglaa

alors dans ce cas, la solution 1 devrait fonctionner...
(celle qui consiste à utiliser la fonction de découpage comme clause d'Order By de la fonction Row_Number)

tu as essayé ?
02/06/2006, 18h27
Yorglaa

Ou si cela ne marche pas, enlève carrément le Order by...

Code:

Row_number() over (Partition by id) as seq

oui j'ai essayé
et j'ai comme résultat

Code:

1
2
3
4
5
6
7
 
ID  column_value  seq
1   A345         1
1   TOTO         2
1   12345        3
2   454          1
3   454          1

Ce qui n'est pas bon puisqu'ilme faut

Code:

1
2
3
4
5
6
ID  column_value  seq
1   12345        1
1   A345         2
1   TOTO         3
2   454          1
3   454          1

Quant à enlever le order by cele ne semble pas possible avec Row_number()

ORA-30485 --> manque l'expression ORDER BY ...

02/06/2006, 22h27
Yorglaa

ben désolé... :(
mais je ne vais plus pouvoir t'aider pour un moment... je pars pour une semaine et demie...

alors bonne chance !
03/06/2006, 17h00
fpouget

Je me permet de faire un "up" car je suis bloqué : aucune des deux méthodes utilisées ne me renvoie correctement les enregistrements dans l'ordre de la sequence de la chaine.

Merci pour vos idées

Pour les suppressions : Utilises le ROW_NUMBER() mais un tri par r (le numrow)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT 
	ID,
	trim(SUBSTR(num, INSTR(num,',',1,rr.r)+1, INSTR(num,',',1,rr.r+1)-INSTR(num,',',1,rr.r)-1 )) AS numdec,
	r, 
	Row_number() OVER (PARTITION BY ID ORDER BY r) AS seq
FROM
	(SELECT ID,
		','||num||',' AS num,
		LENGTH(num)-LENGTH(REPLACE(num,',',''))+1 AS cnt
	FROM mctest2 ) TEST,
	(SELECT ROWNUM r 
	FROM all_objects
	WHERE ROWNUM <  ( SELECT MAX(LENGTH(num)) FROM mctest2)
	) rr
WHERE rr.r <= TEST.cnt
AND trim(SUBSTR(num, INSTR(num,',',1,rr.r)+1, INSTR(num,',',1,rr.r+1)-INSTR(num,',',1,rr.r)-1 )) NOT IN ('12', '13')
ORDER BY 1,3;

06/06/2006, 19h43
fpouget

Merci :D

Cette méthode est même plus rapide (un peu - 1min contre 1min 04 pour le même jeu de données)que l'altenative que j'ai pu donner plus haut.