I am having a Full dump of 1m records arriving every day.I need to obtain this set into STG.
There is soft delete records thus when I compared between Full dump(source) and STG(target), the records in STG always larger than source.
I have research this concern and found the useful sample code that I can customize.
By using MERGE to update records which exist in STG and Insert records which not exist in STG.
Moreover, if the records is not found from Full dump(source) but stay in STG, I need to update flag in STG to be 'Inactive' because I need to keep the history of data in STG.
My Issue is I can flag the Inactive records in STG, however I cannot update all the rest of changing records in STG to followed by from Source.The error is Missing keyword.
create table test_tmp2
(
pk_col int,
col2 char(2),
col3 char(2),
action_flag char(1),
CONSTRAINT test_tmp2_PK PRIMARY KEY (pk_col)
);
create table test_stg2
(
pk_col int,
col2 char(2),
col3 char(2),
action_flag char(1),
CONSTRAINT test_stg2_PK PRIMARY KEY (pk_col)
);
insert into test_tmp2 values (1,'A1','B1','A');
insert into test_tmp2 values (3,'A3','B3','A');
insert into test_stg2 values (1,'A0','B1','A');
insert into test_stg2 values (2,'A2','B2','A');
merge /*+ append */ into test_stg2 O
using (
select * from (
select PK_COL, COL2, COL3,action_flag,
COUNT(*) over(partition by PK_COL) - SUM(Z##_CNT) Z##IUD_FLAG
from (
select PK_COL, COL2, COL3, action_flag,
-1 Z##_CNT
from test_stg2 O
union all
select PK_COL, COL2, COL3,action_flag,
1 Z##_CNT
from test_tmp2 N
)
group by PK_COL, COL2, COL3,action_flag
having SUM(Z##_CNT) != 0
)
where Z##IUD_FLAG < 3
) N
on (O.PK_COL=N.PK_COL)
when matched then update set
o.action_flag = case when N.Z##IUD_FLAG = 2 then 'i' else null end
update set COL2=N.COL2, COL3=N.COL3, action_flag=N.action_flag; -- Here is my issue.
when not matched then insert (PK_COL, COL2, COL3, action_flag)
values(N.PK_COL, N.COL2, N.COL3, N.action_flag);
The result that I'd like to have in STG;
PK_COL, COL2, COL3,action_flag
1,A0,B1,A
2,A2,B2,I
3,A3,B3,A
Best Regards,
You can't have two update clauses in one merge!
And you can only set each column once.
The problem is you're setting rows with Z##IUD_FLAG != 2 to null. It looks to me like all you need to do is return the source action_flag in the else clause of the case.
Doing this gives me the output asked for:
merge into test_stg2 O
using (
select * from (
select PK_COL, COL2, COL3,action_flag,
COUNT(*) over(partition by PK_COL) - SUM(Z##_CNT) Z##IUD_FLAG
from (
select PK_COL, COL2, COL3, action_flag,
-1 Z##_CNT
from test_stg2 O
union all
select PK_COL, COL2, COL3,action_flag,
1 Z##_CNT
from test_tmp2 N
)
group by PK_COL, COL2, COL3,action_flag
having SUM(Z##_CNT) != 0
)
where Z##IUD_FLAG < 3
) N
on (O.PK_COL=N.PK_COL)
when matched then update set
o.action_flag = case when N.Z##IUD_FLAG = 2 then 'I' else N.action_flag end,
COL2=N.COL2, COL3=N.COL3
when not matched then insert (PK_COL, COL2, COL3, action_flag)
values(N.PK_COL, N.COL2, N.COL3, N.action_flag);
select * from test_stg2;
PK_COL COL2 COL3 ACTION_FLAG
1 A1 B1 A
2 A2 B2 I
3 A3 B3 A