Discussion :

[sasafca]

Bonjour à tous,

Je dois mettre en place une requête récursive sur Oracle 12 mais je n'arrive pas au résultat souhaité...
D'ailleurs j'obtiens des résultats assez incohérents avec les données.

Voici ma requête:

Code :

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

1
2
3
4
5
6
7
8
9
	select
      distinct 
      level, DP.ENCADRANT_POSTE_ID, DP.POSTE_ID
     from
     DWH.DP
    where to_date('01/01/2021', 'dd/mm/yyyy') between DP.DATE_DEBUT and DP.DATE_FIN
         start with DP.ENCADRANT_POSTE_ID  = 11010
         CONNECT BY  nocycle PRIOR         DP.POSTE_ID = DP.ENCADRANT_POSTE_ID
         order by 1 desc;

Comme vous l'aurez compris avec la requête, ma table comporte des POSTE_ID avec des ENCADRANT_POSTE_ID (donc le niveau n+1).
Ces postes pouvant évoluer chaque mois, j'ai rajouté la condition sur la date pour n'avoir qu'une ligne pour chaque poste.


Avec le test ci-dessus du poste 11010, j'obtiens des résultats incohérent avec des doublons et des doublons par niveau sans raison que je ne comprenne (si j'enlève Level j'ai du coup plus de doublon ,mais plus de possibilité de m'y retrouver dans les données...).
Par exemple dans mes résultats:

LEVEL	DP.ENCADRANT_POSTE_ID	DP.POSTE_ID
10	62	105
9	62	105
8	62	105
7	62	105

Alors que quand je remonte manuellement requête par requête le poste 105 voici ce que j'obtiens:
105 --> 62 --> 308 --> 1499 --> 1536 --> 1121

Donc il ne fait pas partie directement de ENCADRANT_POSTE_ID = 11010 dont je pensais être partie...

Pouvez-vous m'aider svp ?

Merci !!!

[McM]

Bonjour,

J'ai eu une fois (il y a longtemps) un problème sur une grosse requête récursive, j'ai du filtrer au préalable les données pour faire le connect by dessus
Ensuite, tu peux déjà virer le DISTINCT, et mettre un SYS_CONNECT_BY_PATH(DP.ENCADRANT_POSTE_ID, '/') pour voir les parents

Code :

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

1
2
3
4
5
6
WITH T AS (SELECT encadrant_poste_id, poste_id FROM DWH.DP WHERE TO_DATE('01/01/2021', 'dd/mm/yyyy') BETWEEN date_debut and date_fin)
SELECT LEVEL, encadrant_poste_id, poste_id, SYS_CONNECT_BY_PATH(encadrant_poste_id, '/') Chemin
FROM T
START with encadrant_poste_id  = 11010
CONNECT by nocycle prior poste_id = encadrant_poste_id
ORDER BY 1 desc;

[sasafca]

Tu me sauves la vie !! J'en étais sur que ça venait de la table (assez volumineuse) et que mes filtres fonctionnaient mal...

j'ai une dernière question si jamais tu as une idée pour faire cela proprement:
Maintenant que j'arrive à sortir mes chemins grâce à ta fonction "SYS_CONNECT_BY_PATH(DP.ENCADRANT_POSTE_ID, '/')", il faudrait que j'arrive à organiser tout cela sous la forme d'une table. J'ai normalement 5 niveau.
Du genre:

Niveau 1	Niveau 2	Niveau 3	Niveau 4	Niveau 5
A1	--	--	--	--
A1	B2	--	--	--
A1	C2	--	--	--
A1	D2	--	--	--
A1	B2	C3	--	--
A1	B2	C3	D4	--

Avec A1 qui représente la personne la plus haute dans le classement.

Encore merci !!

[Ikebukuro]

"organiser tout cela sous la forme d'une table" : que veux-tu dire par là? Tu veux que le résultat du SELECT aille alimenter une table? Dans ce cas essaye de faire un CTAS.

[sasafca]

Oui j'aimerai que le résultat de la table soit identique au tableau ci-dessus.
Je ne sais pas du tout ce qu'est le CTAS...

merci

[vanagreg]

Bonjour,

Il faut que tu pivotes tes lignes en colonnes, à l'aide de l'opérateur PIVOT.
Sinon pour ton problème ce serait bien de fournir un jeu d'essai avec le résultat attendu afin que nous puissions tester des solutions.

[Ikebukuro]

Oui j'aimerai que le résultat de la table soit identique au tableau ci-dessus.
Je ne sais pas du tout ce qu'est le CTAS...

merci

CTAS = CREATE TABLE AS SELECT
Tu fais CREATE TABLE TEST01 AS ton_select et, miracle, Oracle crée une table avec les données du SELECT

[JeitEmgie]

Bonjour,

Il faut que tu pivotes tes lignes en colonnes, à l'aide de l'opérateur PIVOT.

le PIVOT va ramener des lignes avec 4 NULL et 1 seule valeur dans une des colonnes:

path	root lvl	L1	L2	L3	L4	L5
/A1	A1	1	A1
/A1/B2	A1	2		B2
/A1/B2/C3	A1	3			C3
/A1/B2/C3/D4	A1	4				D4
/A1/C2	A1	2		C2
/A1/D2	A1	2		D2

Si vous voulez "fusionner", une technique possible est d'encore ajouter un MATCH_RECOGNIZE après le PIVOT
(en fait mettre le PIVOT dans une sous-requête et compléter par un MATCH_RECOGNIZE, et il faut aussi ramener le CONNECT_BY_ROOT aussi pour l'utiliser dans le PARTITION BY
et on a besoin reconstituer le LEVEL - que perd le PIVOT - pour le pattern matching)

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
select ll1,ll2, ll3, ll4, ll5 from (
	select path, nom_racine, nvl2(l1,1, nvl2(l2,2,nvl2(l3,3, nvl2(l4,4,5)))) as lvl, l1, l2, l3, l4, l5  from (
		select level as lvl, poste_id,encadrant_poste_id, nom, connect_by_root(nom) as nom_racine,
			sys_connect_by_path(nom,'/') as path
		from data 
			start with encadrant_poste_id is null
			connect by prior poste_id = encadrant_poste_id
	)
	pivot (
		min(nom) for lvl in (1 as L1,2 as L2,3 as L3,4 as L4,5 as l5)
	)
)
match_recognize (
	partition by nom_racine
	order by path, lvl
	measures R1.L1 as LL1, 
		(case when classifier() >= 'R2' then R2.L2 end) as LL2, 
		(case when classifier() >= 'R3' then R3.L3 end) as LL3, 
		(case when classifier() >= 'R4' then R4.L4 end) as LL4, 
		(case when classifier() >= 'R5' then R5.L5 end) as LL5
	all rows per match
	after match skip to next row
	pattern( R1 (R2|R3|R4|R5)* (R3|R4|R5)* (R4|R5)* R5* )
	define	
		R1 as lvl = 1,
		R2 as lvl = 2,
		R3 as lvl = 3,
		R4 as lvl = 4,
		R5 as lvl = 5		
)

(sans garantie : juste un premier jet, à tester sur un jeu d'essai un peu plus consistant que l'exemple ci-dessus...)

[sasafca]

merci, après plusieurs jours à me casser la tête, je ne suis pas arrivé à faire fonctionner la dernière requête avec la fonction PIVOT...

Voici ma requête actuel:

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 T AS (
select
      FAP.ENCADRANT_POSTE_ID, FAP.POSTE_ID, DF.METIER_NOM, FAP.AGENT_ID, DA.AGENT_NOM, DA.AGENT_PERNOM
     from
     DWH.DTP ,
     DWH.DP ,
     DWH.DSH ,
     DWH.DT ,
     DWH.DO ,
     DWH.DF,
     DWH.DCG,
     DWH.FAP,
     DWH.DA
where ( DP.POSTE_ID = FAP.POSTE_ID and DT.JOUR_DT_DT = FAP.DATE_OCCUP and DO.ORGANISME_CD = FAP.ORGANISME_CD and
         DP.POSTE_TYPE = DTP.POSTE_TYPE_ID and FAP.AGENT_ID = DA.AGENT_ID 
		 and FAP.STRUCT_CD = DSH.SERVICE_CD and FAP.ORGANISME_CD = DSH.COD_COLL
         AND FAP.METIER_ID = DF.METIER_ID AND FAP.CATCADEMPL_CD = DCG.CATGRADE_CD)
)
SELECT LEVEL, SYS_CONNECT_BY_PATH(ENCADRANT_POSTE_ID, '/') Chemin
FROM T
START with T.ENCADRANT_POSTE_ID = -1
CONNECT by nocycle prior T.poste_id = T.ENCADRANT_POSTE_ID
ORDER BY 1 asc;

Ce que je souhaite, c'est découpé le résultat de la colonne en tableau comme décrit plus haut.

Actuellement le résultat de la colonne "chemin" est de la forme:
/-1/1121/825/837/650

Je souhaite que la requête crée une table avec autant de colonne que de "niveau résultats" possible (en se basant sur le max level du coup).
Pour obtenir le tableau suivant:

Niveau 1	Niveau 2	Niveau 3	Niveau 4	Niveau 5
-1	1121	825	837	650

[sasafca]

J'ai réussi à obtenir ce que je souhaité en utilisant la fonction REGEXP_SUBSTR qui permet de spliter une colonne.
Mais du coup ce n'est pas dynamique, si jamais quelqu'un saurait comment faire...

Encore merci à vous tous !