Discussion :

[fpouget]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[sygale]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

, 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 :

Sélectionner tout - Visualiser dans une fenêtre à part

DENSE_RANK() over (Partition by id Order by Num) as seq

[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 !

[Yorglaa]

TEST :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[McM]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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