Scenario: Data Warehouse fact table being updated by values in staging table matching on keys.
This works fine:
UPDATE non_sales.f_retail_history
SET
prm_retail_prc = (select s.prm_retail_prc from non_sales.stage_f_retail_history as s where non_sales.f_retail_history.week_gen_key = s.week_gen_key AND
non_sales.f_retail_history.item_gen_key = s.item_gen_key AND non_sales.f_retail_history.comp_gen_key = s.comp_gen_key)
BUT
We do NOT want to update the column if the value in the staging table is a NULL - we want to retain the value in the fact table column. We have tried NUMEROUS SQL statements to solve this problem but always get a subquery error:
prm_retail_prc = (select case when stg_prm_retail_prc is null and fct_prm_retail_prc is not null then fct_prm_retail_prc else stg_prm_retail_prc end as prm_retail_prc from (select s.prm_retail_prc as stg_prm_retail_prc, f.prm_retail_prc as fct_prm_retail_prc from non_sales.f_retail_history f, non_sales.stage_f_retail_history s where f.week_gen_key = s.week_gen_key and f.item_gen_key = s.item_gen_key and f.comp_gen_key = s.comp_gen_key)x)
I would appreciate any help with this since this is PARAMOUNT in our production processing. I have written a python script but the processing time is untenable for our daily production.
Thanks,
clv100
