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
|
UPDATE TABLE_ONE t1
SET (t1.valeur1, t1.valeur2, t1.valeur3, t1.valeur4, t1.valeur5, t1.valeur6) =
(select substr(STRING_CHARACTERS, 1, 6) as niv1,
substr(STRING_CHARACTERS, 7, 6) as niv2,
substr(STRING_CHARACTERS, 13, 6) as niv3,
substr(STRING_CHARACTERS, 19, 6) as niv4,
substr(STRING_CHARACTERS, 25, 6) as niv5,
substr(STRING_CHARACTERS, 31, 6) as niv6
from (select TABLE_2.*, nvl( case
when TABLE_2.ser_in_agerer = 'O' then TABLE_2.valeur_niv1 || TABLE_2.valeur_niv2 || TABLE_2.valeur_niv3 || TABLE_2.valeur_niv4 || TABLE_2.valeur_niv5 || TABLE_2.valeur_niv6
when jointure1.ser_in_agerer = 'O' then jointure1.valeur_niv1||jointure1.valeur_niv2||jointure1.valeur_niv3||jointure1.valeur_niv4||jointure1.valeur_niv5||jointure1.valeur_niv6
when jointure2.ser_in_agerer = 'O' then jointure2.valeur_niv1||jointure2.valeur_niv2||jointure2.valeur_niv3||jointure2.valeur_niv4||jointure2.valeur_niv5||jointure2.valeur_niv6
when jointure3.ser_in_agerer = 'O' then jointure3.valeur_niv1||jointure3.valeur_niv2||jointure3.valeur_niv3||jointure3.valeur_niv4||jointure3.valeur_niv5||jointure3.valeur_niv6
when jointure4.ser_in_agerer = 'O' then jointure4.valeur_niv1||jointure4.valeur_niv2||jointure4.valeur_niv3||jointure4.valeur_niv4||jointure4.valeur_niv5||jointure4.valeur_niv6
when jointure5.ser_in_agerer = 'O' then jointure5.valeur_niv1||jointure5.valeur_niv2||jointure5.valeur_niv3||jointure5.valeur_niv4||jointure5.valeur_niv5||jointure5.valeur_niv6
end , ' ') as STRING_CHARACTERS
from TABLE_2 TABLE_2
left outer join TABLE_2 jointure1 on jointure1.date_fin_validite = TABLE_2.date_fin_validite and TABLE_2.date_fin_validite = to_date('31/12/2001','DD/MM/YYYY') and jointure1.valeur_niv6 = ' ' and jointure1.valeur_niv5 = TABLE_2.valeur_niv5 and jointure1.valeur_niv4 = TABLE_2.valeur_niv4 and jointure1.valeur_niv3 = TABLE_2.valeur_niv3 and jointure1.valeur_niv2 = TABLE_2.valeur_niv2 and jointure1.valeur_niv1 = TABLE_2.valeur_niv1
left outer join TABLE_2 jointure2 on jointure2.date_fin_validite = TABLE_2.date_fin_validite and TABLE_2.date_fin_validite = to_date('31/12/2001','DD/MM/YYYY') and jointure2.valeur_niv6 = ' ' and jointure2.valeur_niv5 = ' ' and jointure2.valeur_niv4 = TABLE_2.valeur_niv4 and jointure2.valeur_niv3 = TABLE_2.valeur_niv3 and jointure2.valeur_niv2 = TABLE_2.valeur_niv2 and jointure2.valeur_niv1 = TABLE_2.valeur_niv1
left outer join TABLE_2 jointure3 on jointure3.date_fin_validite = TABLE_2.date_fin_validite and TABLE_2.date_fin_validite = to_date('31/12/2001','DD/MM/YYYY') and jointure3.valeur_niv6 = ' ' and jointure3.valeur_niv5 = ' ' and jointure3.valeur_niv4 = ' ' and jointure3.valeur_niv3 = TABLE_2.valeur_niv3 and jointure3.valeur_niv2 = TABLE_2.valeur_niv2 and jointure3.valeur_niv1 = TABLE_2.valeur_niv1
left outer join TABLE_2 jointure4 on jointure4.date_fin_validite = TABLE_2.date_fin_validite and TABLE_2.date_fin_validite = to_date('31/12/2001','DD/MM/YYYY') and jointure4.valeur_niv6 = ' ' and jointure4.valeur_niv5 = ' ' and jointure4.valeur_niv4 = ' ' and jointure4.valeur_niv3 = ' ' and jointure4.valeur_niv2 = TABLE_2.valeur_niv2 and jointure4.valeur_niv1 = TABLE_2.valeur_niv1
left outer join TABLE_2 jointure5 on jointure5.date_fin_validite = TABLE_2.date_fin_validite and TABLE_2.date_fin_validite = to_date('31/12/2001','DD/MM/YYYY') and jointure5.valeur_niv6 = ' ' and jointure5.valeur_niv5 = ' ' and jointure5.valeur_niv4 = ' ' and jointure5.valeur_niv3 = ' ' and jointure5.valeur_niv2 = ' ' and jointure5.valeur_niv1 = TABLE_2.valeur_niv1
where TABLE_2.date_fin_validite = to_date('31/12/2001','DD/MM/YYYY'))
where valeur_niv1 = t1.valeurbis1
and valeur_niv2 = t1.valeurbis2
and valeur_niv3 = t1.valeurbis3
and valeur_niv4 = t1.valeurbis4
and valeur_niv5 = t1.valeurbis5
and valeur_niv6 = t1.valeurbis6
)
WHERE { some conditions } |
Partager