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 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
| select distinct
t1.matric as personne,
t4.valeur as table1,
t2.datedebut as DATE_DEBUT,
/*t10.AISX_DATE_MODIFICATION as Date_modification, t10.AISX_USER_MODIFICATION as Modifie_Par,*/
NVL(to_char(t2.datefin,'dd/mm/yy'),' ') as DATE_FIN,
t3.valeur as MODE_PROBTP,
(
select DECODE(t11.text,'001', 'non transmis', '020', 'transmis', '052', 'signée') as Conv_
from table4 t11
where t11.table4_id92 = '10008'
and t11.table4_idg = t1.id_ent
and t11.table4_id =
(
select max(t21.table4_id)
from table4 t21
where t21.table4_id92 = '1008'
and t21.table4_idg = t1.id_ent
and rownum = 1
and t11.dateAJour is not null
)
) as COntrat,
t2.DATECREATION as DATE_CREATION,
t2.DEROG as DEROGE,
t2.SUPPRIME as SUPPRIME,
NVL
(t10.dateAJour,
(
select max(t10.DATEMODIF)
from table5 t30
left outer join table2 t32 on t32.id_ent = t30.id_ent
left outer join table3 t33 on t33.id = t32.ce_identifiant
left outer join table1 t34 on t34.id = t32.colonne_id
left outer join table4 t31 on t31.table4_idg = t30.id_ent
where rownum = 1
and t31.dateAJour is not null
)
) as DATE_RESP
/*
Problement ici, dans le bloque requete, j'ai essayé de faire un nvl , car si t10.dateAJour est null, ça me renvoie l'arg2,
du coup. j'ai voulé prendre la dernieres date en date ( d'ou mon max) mais j'ai toujours des doublons et je patoge un peu )
*/
from table5 t1
left outer join table2 t2 on t2.id_ent = t1.id_ent
left outer join table3 t3 on t3.id = t2.ce_identifiant
left outer join table1 t4 on t4.id = t2.colonne_id
left outer join table4 t10 on t10.table4_idg = t1.id_ent
where t1.id_entx500 = 2
and mafonction(t1.id_ent, -1, 'Positionne', sysdate) in ('0880','07770')
and NVL
(
(
select 1
from table5 t11
left outer join table2 t12 on t12.id_ent = t11.id_ent
left outer join table3 t13 on t13.id = t12.ce_identifiant
left outer join table1 t14 on t14.id = t12.colonne_id
where t11.id_ent = t1.id_ent
and t12.datefin is null
and rownum = 1
),
0) = 0
order by t1.matric, t2.datedebut; |
Partager