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
| with CO_contact (CO_id, CO_nom, CO_prenom)
as (select 001, 'Dupont ', 'Marcel ' from dual union all
select 002, 'Ben Sousan', 'Fatima ' from dual union all
select 003, 'Mac Gregor', 'Kilian ' from dual union all
select 004, 'Da Silva ', 'Maria ' from dual
)
, IN_info (CO_id, IN_id, IN_numero, IN_type)
as (select 001, 001, '0135456688', 'FD' from dual union all
select 001, 002, '0177889960', 'FW' from dual union all
select 002, 001, '0614151617', 'PP' from dual union all
select 004, 001, '0666771515', 'PW' from dual union all
select 004, 002, '0699887766', 'PP' from dual union all
select 004, 003, '0699151555', 'FD' from dual
)
select CO.CO_nom
, CO.CO_prenom
, I1.IN_numero
, case when I1.IN_type = 'FD' then 'Fixe domicile'
when I1.IN_type = 'FW' then 'Fixe travail'
end Tfixe
, I2.IN_numero
, case when I2.IN_type = 'PP' then 'Mobile perso'
when I2.IN_type = 'FW' then 'Mobile travail'
end Tport
from CO_contact CO
left join IN_info I1
on I1.CO_id = CO.CO_id
and I1.IN_type like 'F%'
left join IN_info I2
on I2.CO_id = CO.CO_id
and I2.IN_type like 'P%'
; |
Partager