Portée d'une table dans sous requête & select fonction
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:
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:
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:
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
