Discussion :

[in]

Bonjour,

je ne sais pas si le titre de mon post correspond vraiment à ce que je veux faire.

Je pense que c'est faisable mais je n'en suis pas sûr ... en tout cas je n'y arrive pas. si vous avez une idée ...

Voici ma requete :

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
with pmb as ( ...
	   )
   ,  cur as ( ...
          )
   , proj as ( ...
         )
SELECT DISTINCT proj.pid, proj.wbs_short_name, proj.wbs_name, pmb.obs_id, o.obs_name res , buResp.obs_name bu
		, first_value(pmb.base_type)  OVER (PARTITION BY pmb.orig_proj_id ORDER BY pmb.add_date DESC) pmbType
		, first_value(pmb.wbs_id) 	 OVER (PARTITION BY pmb.orig_proj_id ORDER BY pmb.add_date DESC) PMB
                , ...
FROM proj LEFT JOIN pmb ON proj.pid = pmb.orig_proj_id
	       LEFT JOIN cur ON proj.pid = cur.orig_proj_id
	      JOIN privuser.project p ON proj.pid = p.proj_id
	      JOIN privuser.obs o ON pmb.obs_id = o.obs_id
	      LEFT JOIN (
				SELECT obs_id, obs_name
				FROM (
					select obs_id, obs_name, level l
					from privuser.obs
					start with obs_id = 18872 --pmb.obs_id   >> ICI
					connect by prior parent_obs_id = obs_id),
					(
					select max(level) m
					from privuser.obs
					start with obs_id = 18872 --pmb.obs_id  >> ICI
					connect by prior parent_obs_id = obs_id) 
					WHERE l = m-3) buResp ON buResp.obs_id = pmb.obs_id  
WHERE p.project_flag='Y'

En fait pour les point notés >> ICI, la requete fonctionne quand je mets des valeurs en dur, mais lorsque je veux utiliser la valeur "dynamique" de mon select (la valeur commentée) je me retrouve avec l'erreur :

ORA-00904: "PMB"."OBS_ID" : identificateur non valide

Ma requete est-elle faisable ?

Merci d'avance

[orafrance]

t'as essayé :

Code :

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

FROM proj LEFT JOIN pmb as mypbm

et référencer mypmb plutôt que pmb ?

Sinon, je ne suis pas trop famillier du code ANSI mais ça devrait pas ressembler à ça :

Code :

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

1
2
3
4
5
FROM proj LEFT JOIN pmb ON proj.pid = pmb.orig_proj_id
	       LEFT JOIN cur ON proj.pid = cur.orig_proj_id
	      JOIN privuser.project p ON proj.pid = p.proj_id
	      JOIN privuser.obs o ON pmb.obs_id = o.obs_id
, pmb  LEFT JOIN (

[in]

t'as essayé :

Code :

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

FROM proj LEFT JOIN pmb as mypbm

Il aime pas du tout la syntaxe mon TOAD ...

FROM proj LEFT JOIN pmb ON proj.pid = pmb.orig_proj_id AS mypmb
=> paranthèse de droite absente

FROM proj LEFT JOIN pmb AS mypmb ON proj.pid = pmb.orig_proj_id
=> mot clé absent

Sinon, je ne suis pas trop famillier du code ANSI mais ça devrait pas ressembler à ça :

Code :

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

1
2
3
4
5
FROM proj LEFT JOIN pmb ON proj.pid = pmb.orig_proj_id
	       LEFT JOIN cur ON proj.pid = cur.orig_proj_id
	      JOIN privuser.project p ON proj.pid = p.proj_id
	      JOIN privuser.obs o ON pmb.obs_id = o.obs_id
, pmb  LEFT JOIN (

Je suis pas sûr ...

en tous cas j'ai essayé et j'obtiens la même erreur

[EDIT] ça vous paraît faisable ? c'est juste un problème de syntaxe ?

[orafrance]

c'est probablement un problème de syntaxe

essaye avec

Code :

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

1
2
from privuser.obs, pmb mypmb
					start with obs_id = mypmb.obs_id

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
with pmb as ( ...
	   )
   ,  cur as ( ...
          )
   , proj as ( ...
         )
SELECT DISTINCT proj.pid, proj.wbs_short_name, proj.wbs_name, pmb.obs_id, o.obs_name res , buResp.obs_name bu
		, first_value(pmb.base_type)  OVER (PARTITION BY pmb.orig_proj_id ORDER BY pmb.add_date DESC) pmbType
		, first_value(pmb.wbs_id) 	 OVER (PARTITION BY pmb.orig_proj_id ORDER BY pmb.add_date DESC) PMB
                , ...
FROM proj LEFT JOIN pmb ON proj.pid = pmb.orig_proj_id
	       LEFT JOIN cur ON proj.pid = cur.orig_proj_id
	      JOIN privuser.project p ON proj.pid = p.proj_id
	      JOIN privuser.obs o ON pmb.obs_id = o.obs_id
	      LEFT JOIN (
				SELECT obs_id, obs_name
				FROM (
					select obs_id, obs_name, level l
					from privuser.obs, pmb mypmb
					start with obs_id = mypmb.obs_id   
					connect by prior parent_obs_id = obs_id),
					(
					select max(level) m
					from privuser.obs, pmb mypmb
					start with obs_id = mypmb.obs_id 
					connect by prior parent_obs_id = obs_id) 
					WHERE l = m-3) buResp ON buResp.obs_id = pmb.obs_id  
WHERE p.project_flag='Y'

[in]

Chapeau bas !

Ca marche !

Par contre ça explose le temps d'exécution ...

Je fais quelques tests encore mais bon, en tout cas ça à l'air !

Merci encore

[EDIT] si vous avez une piste d'amélioration ... parce que là rajouter la table dans le from je me demande si il ne me fait pas un beau produit cartésien.

Je passe de 8 sec à ? (je m'arrête au bout de 5 min) ...

[orafrance]

bah oui mais tout est fenêtré grace au WITH... de toute façon, faut pas réver avec les jointures externe et le CONNECT BY PRIOR du MAX, ça va être difficile d'être performant

[in]

Pourtant quand je mettais une valeur en dur, le petit bout de requête avec les connect by ne prenait que 15 ms. Je m'attendais à avoir une petite baisse de perf mais pas de là à exploser ...

Y aurait-il une manière de n'exécuter ce bout de requête que sur les résultats de la première partie ?

En fait je n'ai que 15 lignes en résultat final, le mieux aurait été que je réussisse à exécuter le "petit bout en plus" sur ces 15 lignes ...

Je vais essayer de mettre tout le début dans un with pour voir ... mais elle va commencer à être sacrément affreuse

Merci en tous cas !

[orafrance]

bah oui, connect by prior sur une valeur ça va forcément beaucoup plus vite que TOUTES les valeurs du WITH

[in]

je me plante grandement il me semble ...

en fait cette dernière jointure, je voudrais la faire sur le résultat de tout ce qu'il y a avant. Je ne veux pas faire le connect by pour chacune des valeur de PMB (du with) ...

Est ce possible ?

J'ai cru un court instant que si je mettais tout dans un with et que je faisais ma jointure ensuite ça ferait ce que je voulais ... mais bon à priori c'est impossible de faire des WITH imbriqués.

[orafrance]

je crois surtout que tu n'as pas compris l'intérêt du WITH

Le WITH c'est comme une vue mais qui serait exécuté qu'une seule fois. Ensuite la requête utilise les lignes de cette vue qui ont été stockées en mémoire.

Essaye ceci (ou un truc ressemblant ) :

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
SELECT * FROM (
with pmb as ( ...
	   )
   ,  cur as ( ...
          )
   , proj as ( ...
         )
SELECT DISTINCT proj.pid, proj.wbs_short_name, proj.wbs_name, pmb.obs_id, o.obs_name res , buResp.obs_name bu
		, first_value(pmb.base_type)  OVER (PARTITION BY pmb.orig_proj_id ORDER BY pmb.add_date DESC) pmbType
		, first_value(pmb.wbs_id) 	 OVER (PARTITION BY pmb.orig_proj_id ORDER BY pmb.add_date DESC) PMB
                , ...
FROM proj LEFT JOIN pmb ON proj.pid = pmb.orig_proj_id
	       LEFT JOIN cur ON proj.pid = cur.orig_proj_id
	      JOIN privuser.project p ON proj.pid = p.proj_id
	      JOIN privuser.obs o ON pmb.obs_id = o.obs_id
) a 
LEFT JOIN (
				SELECT obs_id, obs_name
				FROM (
					select obs_id, obs_name, level l
					from privuser.obs
					start with obs_id = a.obs_id   
					connect by prior parent_obs_id = obs_id),
					(
					select max(level) m
					from privuser.obs
					start with obs_id = a.obs_id 
					connect by prior parent_obs_id = obs_id
                                        ) 
				WHERE l = m-3) buResp ON buResp.obs_id = a.obs_id  
WHERE p.project_flag='Y'

[in]

Oui en fait c'est ce que j'étais en train de faire

par contre je me retrouve un peu au moins point qu'au début, il ne trouve pas la table (a dans votre exemple) => table ou vue inexistante ...

bref, je continue à tripatouiller un peu tout ça ...

merci encore

[orafrance]

déjà :
select obs_id, obs_name, level l
from privuser.obs
start with obs_id = a.obs_id
connect by prior parent_obs_id = obs_id),
(
select max(level) m
from privuser.obs
start with obs_id = a.obs_id
connect by prior parent_obs_id = obs_id
)

Tu peux le remplacer avec une fonction analytique :

Code :

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

1
2
3
4
5
6
7
		SELECT obs_id, obs_name
				FROM (
					select obs_id, obs_name, level l, max(level) OVER (PARTITION BY obs_id) max_level
					from privuser.obs
					start with obs_id = a.obs_id   
					connect by prior parent_obs_id = obs_id)
				WHERE l = max_level-3)

le problème de table doit venir de buResp.obs_id = a.obs_id , c'est pas a mais m

[in]

déjà :
select obs_id, obs_name, level l
from privuser.obs
start with obs_id = a.obs_id
connect by prior parent_obs_id = obs_id),
(
select max(level) m
from privuser.obs
start with obs_id = a.obs_id
connect by prior parent_obs_id = obs_id
)

Tu peux le remplacer avec une fonction analytique :

Code :

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

1
2
3
4
5
6
7
		SELECT obs_id, obs_name
				FROM (
					select obs_id, obs_name, level l, max(level) OVER (PARTITION BY obs_id) max_level
					from privuser.obs
					start with obs_id = a.obs_id   
					connect by prior parent_obs_id = obs_id)
				WHERE l = max_level-3)

en fait les ocntions analytiques, je n'ai découvert leur existence que depuis une semaine, et à vrai dire je capte pas grand chose et je n'ai pas eu le temps de m'y plonger. toutefois la solution proposée n'est pas équivalente. Quand je met une valeur en dure, je n'ai pas le même résulat (en fait je n'ai pas de résultat avec la fonction analytique ... -> max_level doit valoir tout le temps la même valeur pour que ça marche, là il est différent pour chaque ligne)

Mais bon ça à l'ai intéressant, j'essaierai de faire comme ça ...

le problème de table doit venir de buResp.obs_id = a.obs_id , c'est pas a mais m

Là je vois pas trop de m dans votre exemple ...

[in]

Finalement j'ai réussi à utiliser la fonction analytique. En ne précisant rien dans le OVER, le max se fait bien sur toutes les lignes et non plus la ligne courant. Du coup c'est cool, je pense que je vais essayer d'utiliser plus souvent ce type de requete. Mais bon ça reste un peu complexe quand même ...

Sinon pour mon problème initial, j'ai toujours un problème ...

Je met ma requete simplifiée dans l'espoir que quelqu'un puisse voir ce que je fais de travers :

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
select * from 
(  
        with pmb as ( ... ) ...
	SELECT DISTINCT proj.pid, proj.wbs_short_name, proj.wbs_name, pmb.obs_id, o.obs_name res, ...
	FROM proj ...
		JOIN privuser.obs o ON pmb.obs_id = o.obs_id
	WHERE p.project_flag='Y'		  
) liste ,(
	SELECT obs_name
	FROM (
		select obs_name, level l, max(level) OVER () max_level
		from privuser.obs, liste mypmb 
		start with obs_id = mypmb.obs_id
		connect by prior parent_obs_id = obs_id)
	WHERE l = max_level-3)

Là j'obtiens un "table ou vue inexisante".

Si je fais

Code :

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

1
2
3
4
...
from privuser.obs
start with obs_id = liste.obs_id
...

là j'ai ORA-00904: "LISTE"."OBS_ID" : identificateur non valide

si quelqu'un a une petite idée, je sèche vraiment ...