[9ir2] utiliser une valeur du select dans une sous-requete

Version imprimable

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:

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

t'as essayé :

Code:

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:

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 (

Citation:

Envoyé par orafrance

t'as essayé :

Code:

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
Citation:
Envoyé par orafrance
Sinon, je ne suis pas trop famillier du code ANSI mais ça devrait pas ressembler à ça :
Code:

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 ?

c'est probablement un problème de syntaxe :?

essaye avec

Code:

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

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
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'

24/08/2007, 16h40
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 :resolu: !

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) ...
24/08/2007, 16h48
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 :?
24/08/2007, 16h58
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 !
24/08/2007, 17h06
orafrance

bah oui, connect by prior sur une valeur ça va forcément beaucoup plus vite que TOUTES les valeurs du WITH :aie:
24/08/2007, 17h13
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.

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

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 :mrgreen:) :

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
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'

24/08/2007, 17h30
in

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

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 ;)

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:

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 ;)

Citation:
Envoyé par 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:

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 ...

Citation:

Envoyé par orafrance

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 ... 8O

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:

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:

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 ...