Hello,
i'm using Oracle 10.2.0.4.
My problem is the LOG ERRORS part of the SQL. I tried various places but i can't get it to work.
INSERT ALL
INTO cgrsappl.TEMP_MW_POS_LAYER VALUES (num_mandant, dax_generation, cod_rom_asset_class, ' ', 'EUR', ' ', pos80130, amt80130,
cod_bwg_consolidation_ka, cod_bwg_consolidation_tk, cod_bwg_consolidation_sk, NULL)
INTO cgrsappl.TEMP_MW_POS_LAYER VALUES (num_mandant, dax_generation, cod_rom_asset_class, ' ', 'EUR', ' ', pos80140, amt80140,
cod_bwg_consolidation_ka, cod_bwg_consolidation_tk, cod_bwg_consolidation_sk, NULL)
INTO cgrsappl.TEMP_MW_POS_LAYER VALUES (num_mandant, dax_generation, cod_rom_asset_class, ' ', 'EUR', ' ', pos80150, amt80150,
cod_bwg_consolidation_ka, cod_bwg_consolidation_tk, cod_bwg_consolidation_sk, NULL)
INTO cgrsappl.TEMP_MW_POS_LAYER VALUES (num_mandant, dax_generation, cod_rom_asset_class, ' ', 'EUR', ' ', pos80160, amt80160,
cod_bwg_consolidation_ka, cod_bwg_consolidation_tk, cod_bwg_consolidation_sk, NULL)
INTO cgrsappl.TEMP_MW_POS_LAYER VALUES (num_mandant, dax_generation, cod_rom_asset_class, ' ', 'EUR', ' ', pos80170, amt80170,
cod_bwg_consolidation_ka, cod_bwg_consolidation_tk, cod_bwg_consolidation_sk, NULL)
SELECT dax_generation, num_mandant, cod_rom_asset_class,
ROUND(NVL(amt80130,0) * 100,0) amt80130, '80130'||TO_CHAR(LPAD(TO_NUMBER(txt_suffix),2,0)) pos80130,
ROUND(NVL(amt80140,0) * 100,0) amt80140, '80140'||TO_CHAR(LPAD(TO_NUMBER(txt_suffix),2,0)) pos80140,
ROUND(NVL(amt80150,0) * 100,0) amt80150, '80150'||TO_CHAR(LPAD(TO_NUMBER(txt_suffix),2,0)) pos80150,
ROUND(NVL(amt80160,0) * 100,0) amt80160, '80160'||TO_CHAR(LPAD(TO_NUMBER(txt_suffix),2,0)) pos80160,
ROUND(NVL(amt80170,0) * 100,0) amt80170, '80170'||TO_CHAR(LPAD(TO_NUMBER(txt_suffix),2,0)) pos80170,
cod_bwg_consolidation_ka, cod_bwg_consolidation_tk, cod_bwg_consolidation_sk
FROM
(SELECT dax_generation, num_mandant, cod_rom_asset_class, txt_suffix,
SUM(amt80130) amt80130, SUM(amt80140) amt80140, SUM(amt80150) amt80150, SUM(amt80160) amt80160, SUM(amt80170) amt80170,
cod_bwg_consolidation_ka, cod_bwg_consolidation_tk, cod_bwg_consolidation_sk
FROM
(SELECT /*+ USE_HASH(a,b) */a.dax_generation, a.num_mandant, a.cod_rom_asset_class, txt_suffix,
amt80130, amt80140, amt80150, amt80160, amt80170,
NVL(cod_bwg_consolidation_ka,'S') cod_bwg_consolidation_ka,
NVL(cod_bwg_consolidation_tk,'S') cod_bwg_consolidation_tk,
NVL(cod_bwg_consolidation_sk,'S') cod_bwg_consolidation_sk
FROM
(SELECT g_dax_generation dax_generation, g_num_mandant num_mandant, txt_group_1 cod_rom_asset_class, txt_suffix
FROM cgrsadmin.mw_dom_poslayer
WHERE cod_template = 'CR_IRB_E') a
LEFT OUTER JOIN
(SELECT dax_generation, num_mandant, cod_rom_asset_class, cod_rom_rating_grade, rat_pd_calc,
NVL(amt_guarantee,0) amt80130, NVL(amt_derivat,0) amt80140,
(NVL(amt_insurance,0) + NVL(amt_rest_crm,0) + NVL(amt_acc_rec,0)) amt80150,
NVL(amt_fin_crm,0) amt80160, NVL(amt_mortgage_crm,0) amt80170,
cod_bwg_consolidation_ka, cod_bwg_consolidation_tk, cod_bwg_consolidation_sk
FROM cgrsadmin.mw_exp_layer
WHERE dax_generation = g_dax_generation
AND num_mandant = g_num_mandant
AND cod_rom_rating_grade IS NOT NULL
AND yn_cadnet_calc <> 'Y'
AND cod_typ IN ('20','21','22','23')) b
ON a.cod_rom_asset_class = b.cod_rom_asset_class)
GROUP BY num_mandant, dax_generation, cod_rom_asset_class, txt_suffix,
cod_bwg_consolidation_ka, cod_bwg_consolidation_tk, cod_bwg_consolidation_sk)
ORDER BY dax_generation, num_mandant, cod_rom_asset_class, to_number(txt_suffix);
-- LOG ERRORS INTO ERR_TEMP_MW_POS_LAYER ('Fehler '||TO_CHAR(dat_begin,'YYYY.MM.DD HH24:MI:SS')) REJECT LIMIT UNLIMITED;