Hi all,
I have a STATS table with the following structure
The primary key is on columns
EVT_DATE
MSG_PATTERN_ID
SENDER_LIST
RECEIVER_LIST
DOMAIN
ENV
I have on Sender_list and receiver_list values for Greece with two different codes like ‘EL’ and ‘GR’, but I want to replace the EL codes with GR. In case GR records already exists, I want to delete the EL so the replacement not ro raise unique key constraints.
Moreover, I want the value from the merge rows to be added and as hash_key to keep the hash_key from GR.
So, my code is the following :
MERGE INTO STATS tgt
USING(
SELECT ROWID,t.*
, sum(VALUE) over (partition by EVT_DATE, MSG_PATTERN_ID, RECEIVER_LIST, DOMAIN, ENV) SUM_VALUE
, MAX(CASE WHEN SENDER_LIST = 'GR' THEN HASH_KEY END) over (partition by EVT_DATE, MSG_PATTERN_ID, RECEIVER_LIST, DOMAIN, ENV) GR_HASH_KEY
, row_number() over (partition by EVT_DATE, MSG_PATTERN_ID, RECEIVER_LIST, DOMAIN, ENV order by decode(SENDER_LIST,'GR',1,2)) rn
FROM STATS t
WHERE SENDER_LIST in ('EL','GR') ) src
on ( tgt.rowid = src.rowid )
when matched
then
update
set SENDER_LIST = 'GR'
, VALUE = SUM_VALUE
, HASH_KEY = GR_HASH_KEY
delete
where rn>1;
Similarly, for RECEIVER_LIST.
Could you help me please as I get error that ‘ORA-01407: cannot update hash_key to NULL’
Thanks in advnce