Portée d'une table dans sous requête & select fonction

**mercure07** · 20/09/2016, 12h55

Bonjour tout le monde,

Je reviens vers vous pour m'aider à trouver une solution les 2 problèmes suivants :
1) 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
22
23
24
25
26
27
28
29
30
with pres as ( 
              select t1.IDPROD AS spe1 , t2.IDPROD AS spe2 ,t1.INDICELIGNEPRESCRIPTION as id_ligne_1,t2.INDICELIGNEPRESCRIPTION as id_ligne_2,
                    case when t1.TYPEPROD = 5 then 1 else 0 end as typ_prod_1, 
                    case when t2.TYPEPROD =5 then 1 else 0 end as typ_prod_2
                    from PRESCRIPTIONS t1
                    inner join PRESCRIPTIONS t2 on (t1.IDPROD <> t2.IDPROD and t1.ID_ANALYSE = t2.ID_ANALYSE)
                    where  t1.ID_ANALYSE = 1 AND (t1.datedebut is null or t1.datefin is null
                                                or t2.datedebut is null or t2.datefin is null
                                                or ((t1.datedebut <= t2.datefin) and (t1.datefin >= t2.datedebut))
                                                or ((t2.datedebut <= t1.datefin) and (t2.datefin >= t1.datedebut))
                                                )
        )
SELECT  'Q' as ID_TYPE_ALERTE, pres1.*,  t3.FIC_CODE_SQ_PK as ID_FIC_IC, t3.FIC_TEXTE as TEXTE
FROM FIC_INCOMPATIBILITE t3, 
pres pres1, 
pres pres2
WHERE pres1.spe1 <> pres2.spe2 and t3.FIC_TYPE  = 'I'
AND t3.FIC_CODE_SQ_PK IN ( 	
                          SELECT t1.IC1SP_FIC_CODE_FK_PK
                          FROM IC1SP_TERME1SPECIALITE t1
                          inner join ( select IC2SP_FIC_CODE_FK_PK 
                                      from IC2SP_TERME2SPECIALITE ic2
                                      where ic2.IC2SP_SP_CODE_FK_PK = pres2.spe2 ) t2 on t1.IC1SP_FIC_CODE_FK_PK = t2.IC2SP_FIC_CODE_FK_PK
                          WHERE t1.IC1SP_SP_CODE_FK_PK = pres1.spe1										
                          UNION
                          SELECT t1.IC2SP_FIC_CODE_FK_PK
                          FROM (select IC2SP_FIC_CODE_FK_PK from IC2SP_TERME2SPECIALITE where IC2SP_SP_CODE_FK_PK = pres1.spe1) t1
                          inner join IC1SP_TERME1SPECIALITE t2 on t1.IC2SP_FIC_CODE_FK_PK = t2.IC1SP_FIC_CODE_FK_PK
                          WHERE  t2.IC1SP_SP_CODE_FK_PK = pres2.spe2 
									);

renvoie l'erreur

Code :

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

ORA-00904: "PRES2"."SPE2": invalid identifier

. C'est clair que la portée de la table pres2 n'atteint pas les sous requêtes. Comment puis-je contourner cela.

2) Dans la requête suivante, le champs texte renvoyé par la fonction get_reference_url est vide malgré qu'exécutée directement la fonction renvoi une ligne de retour. Sachant que cette fonction retourne un résultat de type CLOB :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Select 'B' as ID_TYPE_ALERTE, GET_REFERENCE_URL(1, fcpmsp.FCPMSP_SP_CODE_FK_PK, fcpmfich.FCPM_CODE_SQ_PK, 'C', fcpmter.FCPMTER_CDF_TER_CODE_FK_PK, fcpmter.FCPMTER_NATURE_CIPEMG_PK, fcpmter.FCPMTER_NUMSEQ_PK) as TEXTE	
			from FCPM_FICHECIPEMG fcpmfich
			inner join FCPMSP_CIPEMG_SPE fcpmsp on fcpmsp.FCPMSP_FCPM_CODE_FK_PK = fcpmfich.FCPM_CODE_SQ_PK
			inner join SP_SPECIALITE sp on fcpmsp.FCPMSP_SP_CODE_FK_PK = sp.sp_code_sq_pk
			inner join PRESCRIPTIONS PRE on (fcpmsp.FCPMSP_SP_CODE_FK_PK = PRE.IDPROD and PRE.ID_ANALYSE = ID_ANALYSE )			
			inner join FCPMTER_FCPM_TERRAIN fcpmter on fcpmter.FCPMTER_FCPM_CODE_FK_PK = fcpmfich.FCPM_CODE_SQ_PK
			inner join CDFPF_LIEN_CDF_PERE_FILS cdfpf on (fcpmter.FCPMTER_CDF_TER_CODE_FK_PK = cdfpf.CDFPF_CODEF_FK_PK 
																and cdfpf.CDFPF_NUMEROF_FK_PK = 'CS' 
																AND cdfpf.CDFPF_NUMEROP_FK_PK = 'TP')
			LEFT join CDF_CODIF cdf1 on (cdfpf.CDFPF_CODEP_FK_PK = cdf1.CDF_CODE_PK and cdf1.CDF_NUMERO_PK = cdfpf.CDFPF_NUMEROP_FK_PK)
			Left join CDF_CODIF cdf2 on (fcpmter.FCPMTER_CDF_TER_CODE_FK_PK = cdf2.CDF_CODE_PK and cdf2.CDF_NUMERO_PK = 'CS')
			where fcpmter.fcpmter_nature_cipemg_pk in (select * from the (select cast(in_list(nature_cipemg) as mytabletype) from dual ))

Merci d'avance pour votre aide et collaboration

**escartefigue** · 20/09/2016, 16h04

Bonjour,

Votre 1ère requête semble plus compliquée que nécessaire, votre prédicat <> utilisé dans la jointure de la CTE peut être remplacé par < pour ne conserver que les valeurs distinctes de couples d'identifiants, en l'état vous ramassez (1,2) et (2,1)

inner join PRESCRIPTIONS t2 on (t1.IDPROD <> t2.IDPROD and t1.ID_ANALYSE = t2.ID_ANALYSE) Devient
inner join PRESCRIPTIONS t2 on (t1.IDPROD <t2.IDPROD and t1.ID_ANALYSE = t2.ID_ANALYSE).

Les UNION sans "all" sont elles nécessaires ?

Je doute qu'en l'état les temps de réponse (après correction) soient acceptables prédicats "<>", union, OR etc...

Enfin et surtout, quel est le but fonctionnel de tout ça, là vous présentez le comment mais pas le pourquoi

**skuatamad** · 20/09/2016, 17h14

Pour la 1ere requête le niveau de portée de pres2 est la sous-requête (le IN) et donc ça plante pour lors de l'utilisation dans T2.
On en est au 2eme niveau de sous-requête, mais la sous-requête t2 semble inutile, non ?

Is there some sort of nesting limit for correlated subqueries?

**mercure07** · 20/09/2016, 17h25

Merci pour votre réponses.

Pour la 1ère requête, le problème est au niveau de la ligne 24. En fait, la table pres n'est pas reconnue dans la sous requête du Where et du coup j'ai l'erreur

Code :

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

PRES2"."SPE2": invalid identifier

.

J'ai utilisé Union all au lieu de union pour afficher le champs texte de type CLOB retourné par la fonction GET_REFERENCE_URL.

Il s'agit d'un PS très complexe qui depuis des données patients retourne les éventuelles alertes.

**mercure07** · 21/09/2016, 08h36

@skuatamad Merci pour votre réponse.

Cependant, je ne pense qu'il y a une table ou jointure inutile dans la 1ère requête. En fait, la partie With qui renvoie pres permet de détecter les prescriptions chevauchantes.

La 2ème partie de la requête permet de récupérer les colonnes qui répondent au critères demandés tout en se basant sur les résultats renvoyés par "pres".

**skuatamad** · 21/09/2016, 14h24

Ce qui me semble inutile c'est la sous-requête t2 :

Code :

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

1
2
3
4
5
6
SELECT t1.IC1SP_FIC_CODE_FK_PK
                          FROM IC1SP_TERME1SPECIALITE t1
                          inner join ( select IC2SP_FIC_CODE_FK_PK 
                                      from IC2SP_TERME2SPECIALITE ic2
                                      where ic2.IC2SP_SP_CODE_FK_PK = pres2.spe2 ) t2 on t1.IC1SP_FIC_CODE_FK_PK = t2.IC2SP_FIC_CODE_FK_PK
                          WHERE t1.IC1SP_SP_CODE_FK_PK = pres1.spe1

Qui pourrait devenir :

Code :

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

1
2
3
4
5
SELECT t1.IC1SP_FIC_CODE_FK_PK
  FROM IC1SP_TERME1SPECIALITE t1
  join IC2SP_TERME2SPECIALITE t2 on t1.IC1SP_FIC_CODE_FK_PK = t2.IC2SP_FIC_CODE_FK_PK
  WHERE t1.IC1SP_SP_CODE_FK_PK = pres1.spe1
    and t2.IC2SP_SP_CODE_FK_PK = pres2.spe2

Par ailleurs dans votre requête principale vous faites un produit cartésien entre les pres1, pres2 et FIC_INCOMPATIBILITE t3... Est-ce vraiment volontaire ?

Vous devriez exposer votre problématique avec un jeu d'essai et indiquer le résultat désiré.

**mercure07** · 22/09/2016, 15h14

Merci pour votre réponse. Vous avez raison pour la sous requête t2 qui est effectivement inutile. J'ai modifié la requête et ci-après la nouvelle version

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 pres as ( 
              select /*+ MATERIALIZE */ t1.IDPROD AS spe1 , t2.IDPROD AS spe2 ,t1.INDICELIGNEPRESCRIPTION as id_ligne_1,t2.INDICELIGNEPRESCRIPTION as id_ligne_2,
                    case when t1.TYPEPROD = 5 then 1 else 0 end as typ_prod_1, 
                    case when t2.TYPEPROD =5 then 1 else 0 end as typ_prod_2
                    from PRESCRIPTIONS t1
                    inner join PRESCRIPTIONS t2 on (t1.IDPROD <t2.IDPROD and t1.ID_ANALYSE = t2.ID_ANALYSE)
                    where  t1.ID_ANALYSE = 1 AND t1.IDPROD <> t2.IDPROD 
                    and (t1.datedebut is null or t1.datefin is null
                        or t2.datedebut is null or t2.datefin is null
                        or ((t1.datedebut <= t2.datefin) and (t1.datefin >= t2.datedebut))
                        or ((t2.datedebut <= t1.datefin) and (t2.datefin >= t1.datedebut))
                       )
        )
SELECT  'Q' as ID_TYPE_ALERTE, pres1.*,  t3.FIC_CODE_SQ_PK as ID_FIC_IC, t3.FIC_TEXTE as TEXTE
FROM FIC_INCOMPATIBILITE t3, 
pres pres1,
pres pres2
WHERE t3.FIC_TYPE  = 'I'
AND t3.FIC_CODE_SQ_PK in ( select ic1.ic1sp_fic_code_fk_pk
                           from ic1sp_terme1specialite ic1
                           inner join ic2sp_terme2specialite ic2 on ic2.ic2sp_sp_code_fk_pk = pres2.spe2
                                                                and ic1.ic1sp_fic_code_fk_pk   = ic2.ic2sp_fic_code_fk_pk 
                           union
                           select ic2.IC2SP_FIC_CODE_FK_PK
                           from IC2SP_FIC_CODE_FK_PK ic2
                           inner join IC1SP_TERME1SPECIALITE ic1 on ic2.IC2SP_SP_CODE_FK_PK = pres1.spe1 
                                                                and ic2.IC2SP_FIC_CODE_FK_PK = ic1.IC1SP_FIC_CODE_FK_PK 
         );

mais l'erreur
[CODE]ORA-00904: "PRES2"."SPE2": invalid identifier[CODE] persiste toujours.

Par ailleurs dans votre requête principale vous faites un produit cartésien entre les pres1, pres2 et FIC_INCOMPATIBILITE t3... Est-ce vraiment volontaire ?

En fait, je dois vérifier les alertes d'incompatibilité entre médicaments prescrits chevauchants. C'est pour cela que le With permet de récupérer ces prescriptions chevauchantes et par la suite vérifier s'il y a des incompatibilités entre ces prescriptions.

**mercure07** · 26/09/2016, 11h21

Merci pour votre réponse. Vous avez raison pour la sous requête t2 qui est effectivement inutile. J'ai modifié la requête et ci-après la nouvelle version

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 pres as ( 
              select /*+ MATERIALIZE */ t1.IDPROD AS spe1 , t2.IDPROD AS spe2 ,t1.INDICELIGNEPRESCRIPTION as id_ligne_1,t2.INDICELIGNEPRESCRIPTION as id_ligne_2,
                    case when t1.TYPEPROD = 5 then 1 else 0 end as typ_prod_1, 
                    case when t2.TYPEPROD =5 then 1 else 0 end as typ_prod_2
                    from PRESCRIPTIONS t1
                    inner join PRESCRIPTIONS t2 on (t1.IDPROD <t2.IDPROD and t1.ID_ANALYSE = t2.ID_ANALYSE)
                    where  t1.ID_ANALYSE = 1 AND t1.IDPROD <> t2.IDPROD 
                    and (t1.datedebut is null or t1.datefin is null
                        or t2.datedebut is null or t2.datefin is null
                        or ((t1.datedebut <= t2.datefin) and (t1.datefin >= t2.datedebut))
                        or ((t2.datedebut <= t1.datefin) and (t2.datefin >= t1.datedebut))
                       )
        )
SELECT  'Q' as ID_TYPE_ALERTE, pres1.*,  t3.FIC_CODE_SQ_PK as ID_FIC_IC, t3.FIC_TEXTE as TEXTE
FROM FIC_INCOMPATIBILITE t3, 
pres pres1,
pres pres2
WHERE t3.FIC_TYPE  = 'I'
AND t3.FIC_CODE_SQ_PK in ( select ic1.ic1sp_fic_code_fk_pk
                           from ic1sp_terme1specialite ic1
                           inner join ic2sp_terme2specialite ic2 on ic2.ic2sp_sp_code_fk_pk = pres2.spe2
                                                                and ic1.ic1sp_fic_code_fk_pk   = ic2.ic2sp_fic_code_fk_pk 
                           union
                           select ic2.IC2SP_FIC_CODE_FK_PK
                           from IC2SP_FIC_CODE_FK_PK ic2
                           inner join IC1SP_TERME1SPECIALITE ic1 on ic2.IC2SP_SP_CODE_FK_PK = pres1.spe1 
                                                                and ic2.IC2SP_FIC_CODE_FK_PK = ic1.IC1SP_FIC_CODE_FK_PK 
         );

mais l'erreur
[CODE]ORA-00904: "PRES2"."SPE2": invalid identifier[CODE] persiste toujours.

Par ailleurs dans votre requête principale vous faites un produit cartésien entre les pres1, pres2 et FIC_INCOMPATIBILITE t3... Est-ce vraiment volontaire ?

En fait, je dois vérifier les alertes d'incompatibilité entre médicaments prescrits chevauchants. C'est pour cela que le With permet de récupérer ces prescriptions chevauchantes et par la suite vérifier s'il y a des incompatibilités entre ces prescriptions.

Vous trouvez-ci dessous la structure des tables utilisées dans la requête :

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
33
34
35
36
37
38
39
40
41
42
43
44
CREATE TABLE FIC_INCOMPATIBILITE
(
  FIC_CODE_SQ_PK Integer NOT NULL,
  FIC_ORIGINE Char(1) NOT NULL,
  FIC_TYPE 	Char(1) NOT NULL,
  FIC_TEXTE Clob ,
  FIC_DATECR Date NOT NULL,
  FIC_DATEMJ Date,
  CONSTRAINT PK_FIC_INCOMPATIBILITE PRIMARY KEY (FIC_CODE_SQ_PK)
);
 
CREATE TABLE IC1SP_TERME1SPECIALITE
(
  IC1SP_FIC_CODE_FK_PK Integer NOT NULL,
  IC1SP_SP_CODE_FK_PK Integer NOT NULL,
  CONSTRAINT PK_IC1SP_TERME1SPECIALITE PRIMARY KEY (IC1SP_FIC_CODE_FK_PK,IC1SP_SP_CODE_FK_PK)
);
 
ALTER TABLE IC1SP_TERME1SPECIALITE ADD CONSTRAINT FK1_IC1SP_TERME1SPECIALITE
  FOREIGN KEY (IC1SP_FIC_CODE_FK_PK) REFERENCES FIC_INCOMPATIBILITE (FIC_CODE_SQ_PK) ON DELETE CASCADE ENABLE;
 
CREATE TABLE IC1SP_TERME1SPECIALITE
(
  IC1SP_FIC_CODE_FK_PK Integer NOT NULL,
  IC1SP_SP_CODE_FK_PK Integer NOT NULL,
  CONSTRAINT PK_IC1SP_TERME1SPECIALITE PRIMARY KEY (IC1SP_FIC_CODE_FK_PK,IC1SP_SP_CODE_FK_PK)
);
 
ALTER TABLE IC1SP_TERME1SPECIALITE ADD CONSTRAINT FK1_IC1SP_TERME1SPECIALITE
  FOREIGN KEY (IC1SP_FIC_CODE_FK_PK) REFERENCES FIC_INCOMPATIBILITE (FIC_CODE_SQ_PK) ON DELETE CASCADE;
 
CREATE TABLE PRESCRIPTIONS (
	ID_ANALYSE					INT NOT NULL,
	INDICELIGNEPRESCRIPTION 	INT NOT NULL,
	DATEDEBUT 					DATE,
	DATEFIN 					DATE,
	IDPROD 						VARCHAR2(20),
	TYPEPROD 					INTEGER,
	CONTENANCE_UD 				VARCHAR2(20),
	UNITE_CONTENANCE 			VARCHAR2(30),
	VECTEUR_INJ  				VARCHAR2(200),
	MATERIAU_CONT_INJ 			VARCHAR2(100),
	CONSTRAINT PRESCRIPTIONS_PK PRIMARY KEY (ID_ANALYSE, INDICELIGNEPRESCRIPTION)
);

Portée d'une table dans sous requête & select fonction

SQL Oracle

Discussions similaires

Partager

Partager