Hello everybody,
I've 2 problems with on 2 queries :
1) The following query
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
); returns
ORA-00904: "PRES2"."SPE2": invalid identifier
error. I've searched and found that Oracle don't permiss 2 level of subqueries. But I'm forced to preserve this structure. In fact, I'm using the "pres" records to retreive the results.
Can anyone tell me how to change this query ?
2) The second problm is on the following query
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 )) The problem is that I'm selecting the result of a function (GET_REFERENCE_URL)but Data are empty for the colomn TEXTE. Also, in this query I'm forced to use union to select CLOB data but as you know Oracle dont' permiss this. Unsign union all gives dupllicated records and I'cant do a dinstint on CLOB Data. Can anyone tell how to proceed ?
Many thanks in advance for your help.
Best regard.
1. I don't understand why you need a subquery in this join:
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
Surely you can replace it with something like:
select t1.ic1sp_fic_code_fk_pk
from ic1sp_terme1specialite t1
inner join ic2sp_terme2specialite ic2
on ic2.ic2sp_sp_code_fk_pk = pres2.spe2
and t1.ic1sp_fic_code_fk_pk = t2.ic2sp_fic_code_fk_pk;
thus avoiding the need to join 2 levels deep?
2. You can return one of the clobs by using dbms_lob.compare to find the matching values in a subquery, then filtering to return the min of these:
create table t (
x clob
);
insert into t values ('clob');
insert into t values ('clob');
insert into t values ('blah');
select distinct x from t;
SQL Error: ORA-00932: inconsistent datatypes: expected - got CLOB
select x from t t
where rowid in (
select min(rowid)from t s
where dbms_lob.compare(t.x, s.x) = 0
);
X
clob
blah