Discussion :

[orus8]

Bonjour à tous,

J'ai un problème avec une requête sous Oracle, je ne sais pas comment m'y prendre.

J'ai une table NOMENCLATURE dans laquelle j'ai ces champs :

• Composé
• Taille de lot
• Composant
• Quantité composant

Exemple :

Code :

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

1
2
3
4
5
6
7
Composé		Taille de lot	Composant	Quantité composant
A		10		A1		2
A		20		A1		4
A		10		A2		10
A		10		A3		5
A		20		A2		20
A		20		A3		10

Pour l'exemple je n'ai mis qu'un seul composé mais évidemment dans la table NOMENCLATURE il y a plusieurs composés.

Je souhaite dans une requête ne récupérer, pour chaque composé (ici il n'y a que le composé A), que les lignes associées à la taille de lot max du composé. Par exemple ici la taille de lot max du composé A est 20, je veux donc uniquement récupérer pour le composé A les lignes avec une taille de lot valant 20. Pour notre exemple, j'ai mis en rouge les lignes à ne pas afficher et en vert celles à conserver.

Code :

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

1
2
3
4
5
6
7
Composé		Taille de lot	Composant	Quantité composant
A		10		A1		2
A		20		A1		4
A		10		A2		10
A		10		A3		5
A		20		A2		20
A		20		A3		10

Auriez-vous une idée pour traduire ça sous forme de requête SQL Oracle ?

Je vous remercie par avance

[Waldar]

Par exemple :

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
22
23
with nomenclature as
(
select 'A' c1, 10 tl, 'A1' c2,  2 qt from dual union all
select 'A'   , 20   , 'A1'   ,  4    from dual union all
select 'A'   , 10   , 'A2'   , 10    from dual union all
select 'A'   , 10   , 'A3'   ,  5    from dual union all
select 'A'   , 20   , 'A2'   , 20    from dual union all
select 'A'   , 20   , 'A3'   , 10    from dual
)
  ,  sr as
(
select c1, tl, c2, qt,
       rank() over(order by tl desc) as rk
  from nomenclature
)
select c1, tl, c2, qt
  from sr
 where rk = 1;
 
C1	TL	C2	QT
A	20	A1	4
A	20	A2	20
A	20	A3	10

[mikedavem]

Bonjour,

Comme ceci (si vous êtes en version 8.0 d'oracle et que la fonction RANK() n'est pas supportée )

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
WITH nomenclature AS
(
SELECT 'A' c1, 10 tl, 'A1' c2,  2 qt union ALL
SELECT 'A'   , 20   , 'A1'   ,  4    union ALL
SELECT 'A'   , 10   , 'A2'   , 10    union ALL
SELECT 'A'   , 10   , 'A3'   ,  5    union ALL
SELECT 'A'   , 20   , 'A2'   , 20    union ALL
SELECT 'A'   , 20   , 'A3'   , 10    
),  
sr AS
(
	SELECT 
		c1, tl, c2, qt, 
		(SELECT COUNT(DISTINCT tl) FROM nomenclature n2 WHERE n2.tl < n1.tl) AS rk
	FROM nomenclature n1
)
SELECT c1, tl, c2, qt,rk
  FROM sr
WHERE rk = 1;

++

[orus8]

Merci pour vos réponses.
En vérifiant nous sommes bien en version 9.

La requête ne fonctionne pas tout à fait comme je le souhaite. En effet, voici mon problème.

Un composant peut également être un composé. La fonction rank ne trie donc pas correctement car elle se base sur la taille de lot de tous les composés sans faire de distinction.

Il faudrait que la requête affiche pour chaque composé, tous les composants ayant une taille de lot de rang 1. En reprenant notre précédent exemple, j'ai ajouté quelques composés pour mettre en évidence ce que je souhaite voir afficher (en vert). En rouge sont représentés les lignes à ne pas afficher.

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
Composé		Taille de lot	Composant	Quantité composant	Rang
----------------------------------------------------------------------------
A		10		A1		2			2
A		20		A1		4			1
A		10		A2		10			2
A		10		A3		5			2
A		20		A2		20			1
A		20		A3		10			1
----------------------------------------------------------------------------
A1		100		A11		50			2
A1		200		A12		100			1
----------------------------------------------------------------------------
A3		500		A311		5			1
A3		500		A312		10			1
A3		50		A32		0,5			3
A3		100		A33		1			2

Auriez-vous une idée ?

Je vous remercie par avance.

[Waldar]

Auriez-vous une idée ?

Si vous aviez regardé les documentations et tutoriaux relatifs à cette fonction, vous auriez trouvé tout seul :

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
22
23
24
25
26
27
28
29
30
31
32
WITH nomenclature AS
(
SELECT 'A' c1,  10 tl, 'A1' c2,   2 qt FROM dual union ALL
SELECT 'A'   ,  20   , 'A1'   ,   4    FROM dual union ALL
SELECT 'A'   ,  10   , 'A2'   ,  10    FROM dual union ALL
SELECT 'A'   ,  10   , 'A3'   ,   5    FROM dual union ALL
SELECT 'A'   ,  20   , 'A2'   ,  20    FROM dual union ALL
SELECT 'A'   ,  20   , 'A3'   ,  10    FROM dual union ALL
SELECT 'A1'  , 100   , 'A11'  ,  50    FROM dual union ALL
SELECT 'A1'  , 200   , 'A12'  , 100    FROM dual union ALL
SELECT 'A3'  , 500   , 'A311' ,   5    FROM dual union ALL
SELECT 'A3'  , 500   , 'A312' ,  10    FROM dual union ALL
SELECT 'A3'  ,  50   , 'A32'  ,   0.5  FROM dual union ALL
SELECT 'A3'  , 100   , 'A33'  ,   1    FROM dual
)
  ,  sr AS
(
SELECT c1, tl, c2, qt,
       rank() over(PARTITION BY c1 ORDER BY tl DESC) AS rk
  FROM nomenclature
)
SELECT c1, tl, c2, qt
  FROM sr
 WHERE rk = 1;
 
C1	TL	C2	QT
A	20	A1	4
A	20	A2	20
A	20	A3	10
A1	200	A12	100
A3	500	A311	5
A3	500	A312	10

Comme ceci (si vous êtes en version 8.0 d'oracle et que la fonction RANK() n'est pas supportée )

Les CTE non plus alors

[mikedavem]

Les CTE non plus alors

C'est vrai .. c'est une solution que je qualifierais d'hybride ...

[orus8]

Merci à vous, ça fonctionne parfaitement

Effectivement je n'avais pas regardé plus en détail dans la documentation mais je ne pensais pas que c'était possible de cette manière.

Merci beaucoup.

[nico1407]

Vous pouvez tester ceci sinon.

Code :

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

1
2
3
SELECT * FROM NOMANCLATURE
WHERE Compose || Taille IN
(SELECT compose || tailleMax FROM (SELECT compose, MAX(taille) AS tailleMax FROM NOMENCLATURE GROUP BY compose));