select p, d, t, q, c, s, h, n from (
select * from (
with wt1 as (select 'n' p,3 d,7 t,4 q,5 c, 6 s, 1 h, 1 n from dual),
wt2 as (select 'e' p,9 d,6 t,7 q,1 c, 0 s, 3 h, 4 n from dual)
select case when t1.p = t6.p then t1.p else wt2.p end p,
decode(wt1.d, t1.d, t7.n, t2.c, t4.s, t6.t, t7.d, t8.c) d,
max(greatest(t2.d, t6.q, t7.c, t8.h, t5.n, t1.s, t1.d, t2.q, t7.h)) t,
min(least(t4.c, t5.d, t8.h, wt2.n, t4.s, t1.c, t7.s, t5.d)) q,
decode(sign(t2.q-t4.d), -1, t4.q, decode(sign(t8.n-t3.s), -1, t2.n, decode(sign(t2.t-t4.h), 0, t2.h, t5.c))) c,
case when exists (
select 1
from wt1, wt2
where wt1.t = wt2.q
) then wt1.n else wt2.d end s,
to_number(substr(concat(t5.d, t6.h) || t3.d || concat(t7.s, t1.q) || substr(concat(t8.d, t3.s) || t7.c * t3.c * t4.s, -4,1),-1,1)) h,
to_number(substr((replace(replace(to_char((t3.h * t4.t * t6.c + t2.d + t1.h * t8.q * (t6.n * (to_date('01/01/2017', 'dd/mm/yyyy')-to_date('01/01/2016', 'dd/mm/yyyy'))))), '3', '9'), '9', '4'))*3, -1,1)) n,
0 r
from
(select 'n' p,4 d,3 t,0 q,9 c, 3 s, 1 h, 3 n from dual) t1,
(select 'e' p,4 d,2 t,4 q,3 c, 2 s, 6 h, 7 n from dual) t2,
(select 'e' p,6 d,6 t,5 q,9 c, 7 s, 4 h, 1 n from dual) t3,
(select 'n' p,1 d,8 t,1 q,4 c, 4 s, 2 h, 9 n from dual) t4,
(select 'e' p,6 d,9 t,3 q,6 c, 3 s, 1 h, 0 n from dual) t5,
(select 'n' p,9 d,4 t,4 q,3 c, 1 s, 9 h, 4 n from dual) t6,
(select 'n' p,4 d,1 t,3 q,8 c, 5 s, 3 h, 3 n from dual) t7,
(select 'e' p,1 d,9 t,4 q,0 c, 7 s, 7 h, 2 n from dual) t8,
wt1, wt2
group by case when t1.p = t6.p then t1.p else wt2.p end ,
decode(wt1.d, t1.d, t7.n, t2.c, t4.s, t6.t, t7.d, t8.c) ,
decode(sign(t2.q-t4.d), -1, t4.q, decode(sign(t8.n-t3.s), -1, t2.n, decode(sign(t2.t-t4.h), 0, t2.h, t5.c)))
union all
(
select t9.*, rank() over(order by (mod(t9.h+t9.t*t9.q+t9.c+t9.n,2))) r from
(select 'e' p,0 d,2 t,1 q,1 c, 3 s, 1 h, 3 n from dual
union
select 'e',0,2,1,2, 2, 6, 5 from dual
union
select 'e',0,2,1,2, 4, 2, 3 from dual
union
select 'e',0,2,1,2, 0, 4, 1 from dual
union
select 'e',0,2,1,1, 7, 3, 9 from dual
union
select 'e',0,2,1,1, 9, 8, 3 from dual
union
select 'e',0,2,1,1, 5, 1, 1 from dual
union
select 'e',0,2,1,1, 9, 1, 3 from dual
) t9
)) a
where r = 0 or decode(a.p, 'n', 0, 1) = (
select decode(sign(instr(regexp_replace(substr(ch, instr(ch, (select substr(to_char(to_date('01/01/2015', 'dd/mm/yyyy') - 31, 'month'),1,1) from dual)), instr(ch, '?')), '[a-df-z]', 'n'),'e')), 1, 1, 0) t
from (
select 'mais quel est le ' || chr(100) || chr(105) || chr(110) || chr(103) || chr(117) || chr(101) || ' qui a écrit ça ?' ch from dual
)
)
) b
where mod(b.r, 2) <> 0 or r = 0
