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
| MERGE INTO QH_REF QH
USING (SELECT TO_NUMBER (TO_CHAR ((SELECT dtvaloref FROM tdbfi_ophyr_dts), 'YYYY')) PASTRF,
TO_NUMBER (TO_CHAR ((SELECT dtvaloref FROM tdbfi_ophyr_dts), 'MM')) PMSTRF,
TO_NUMBER (TO_CHAR ((SELECT dtvaloref FROM tdbfi_ophyr_dts), 'DD')) PJSTRF, cnt.ct_itu,
cnt.ll_typ_itu, cnt.typinst, cnt.libtyins, cnt.cd_sou_typ_cnt,
cnt.ll_sou_typ_cnt, cnt.codeisin, cnt.libval, cnt.dd_cnt,
cnt.df_cnt, cnt.cd_fam_rsq, cnt.ll_fam_rsq, cnt.tx_pln, cnt.mt_plf,
va_ect, cnt.da_opt_swa, cnt.typtx, cnt.tx_fix_cnt,
cnt.cd_tau_ref_var, cnt.cd_frq_int, cnt.cd_bas_cal_div, cnt.nomi,
cnt.cd_dev, cnt.fl_swa, ay_nio_tie, cd_ric_tie, cd_ric_grp_tie,
cd_sec_bay_tie, cd_sec_gic_tie, cd_sec_nac_tie, da_not_ctm_fitch,
da_not_ctm_mood, da_not_ctm_sp, da_not_ltm_fitch, da_not_ltm_mood,
da_not_ltm_sp, da_ten_ctm_fitch, da_ten_ctm_mood, da_ten_ctm_sp,
da_ten_ltm_fitch, da_ten_ltm_mood, da_ten_ltm_sp, ll_gar_tie,
cd_grp_tie, ll_sec_bay_tie, ll_sec_gic_tie, ll_sec_nac_tie, ll_tie,
ll_grp_tie, ll_nio_tie, ll_ric_tie, ll_ric_grp_tie, ll_ent_tie,
ll_nat_tie, cd_not_tie_ctm_fitch, cd_not_tie_ctm_mood,
cd_not_tie_ctm_sp, cd_not_tie_ltm_fitch, cd_not_tie_ltm_mood,
cd_not_tie_ltm_sp, cd_rle_tie, ll_pay, fl_zon_eur, cd_ten_ctm_fitch,
cd_ten_ctm_mood, cd_ten_ctm_sp, cd_ten_ltm_fitch, cd_ten_ltm_mood,
cd_ten_ltm_sp, fl_tie_eea, fl_tie_emg, id_tie, fl_tie_ocd,
fl_tie_zon_eur, cd_ent_tie, j1.ll_typ_jam, j1.lc_sen_jam,
j1.cd_sja_jam, j1.ct_sja_jam, j1.lc_typ_sja_jam, j1.ll_jam,
j1.va_ect_jam, j1.ct_tau_jam, j1.tx_fix_jam, j1.cd_tau_ref_var,
j1.cd_frq_int, j1.cd_bas_cal_div, j1.mt_nom_jam, j1.cd_dev_jam,
j1.ll_typ_cll_jam, j2.ll_typ_jam, j2.lc_sen_jam, j2.cd_sja_jam,
j2.ct_sja_jam, j2.lc_typ_sja_jam, j2.ll_jam, j2.va_ect_jam,
j2.ct_tau_jam, j2.tx_fix_jam, j2.cd_tau_ref_var, j2.cd_frq_int,
j2.cd_bas_cal_div, j2.mt_nom_jam, j2.cd_dev_jam, j2.ll_typ_cll_jam
FROM tdbfi_ophyr_04 tiers,
tdbfi_ophyr_02 cnt,
(SELECT * FROM tdbfi_ophyr_03 l WHERE l.lc_sen_jam = 'RECU') j1,
(SELECT * FROM tdbfi_ophyr_03 f WHERE f.lc_sen_jam = 'VERSE') j2
WHERE cnt.ct_itu = 'SWAP'
AND cnt.codeisin = j1.codeisin(+)
AND cnt.codeisin = j2.codeisin(+)
AND cnt.id_tie_ctp = tiers.id_tie(+)) C
ON
(QH.PASTRF=C.PASTRF
and QH.PMSTRF=C.PMSTRF
and QH.PJSTRF=C.PJSTRF
and QH.CODEISIN= C.CODEISIN
)
WHEN MATCHED THEN UPDATE SET QH.CD_FAM_RSQ = C.CD_FAM_RSQ
WHEN NOT MATCHED THEN
INSERT
(QH.CD_FAM_RSQ)
VALUES
(C.CD_FAM_RSQ) |
Partager