You Asked

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,

and Chris said...

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

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

on Full outer joins

Rajeshwaran Jeyabal, June 05, 2019 - 12:44 pm UTC

given that both the table has a PK column and stated the requirement of having a flag condition like this.
..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...

a full outer join between the table would do the steps nicely like this

demo@PDB1> select * from test_tmp2;

    PK_COL CO CO A
---------- -- -- -
         1 A1 B1 A
         3 A3 B3 A

demo@PDB1> select * from test_stg2;

    PK_COL CO CO A
---------- -- -- -
         1 A0 B1 A
         2 A2 B2 A

demo@PDB1> select nvl(t1.pk_col,t2.pk_col) as pk_col,
  2         nvl(t1.col2,t2.col2) as col2,
  3         nvl(t1.col3,t2.col3) as col3,
  4      case when t1.pk_col is null then 'I'
  5          else 'A' end as new_flag
  6  from test_tmp2 t1
  7     full outer join
  8     test_stg2 t2
  9  on (t1.pk_col = t2.pk_col ) ;

    PK_COL CO CO N
---------- -- -- -
         1 A1 B1 A
         2 A2 B2 I
         3 A3 B3 A

then finally merge them into the Target table like this.

demo@PDB1> merge into test_stg2 x1 using ( select nvl(t1.pk_col,t2.pk_col) as pk_col,
  2         nvl(t1.col2,t2.col2) as col2,
  3         nvl(t1.col3,t2.col3) as col3,
  4      case when t1.pk_col is null then 'I'
  5          else 'A' end as new_flag
  6  from test_tmp2 t1
  7     full outer join
  8     test_stg2 t2
  9  on (t1.pk_col = t2.pk_col ) ) x2
 10  on (x1.pk_col = x2.pk_col)
 11  when matched then
 12     update set
 13     x1.col2 = x2.col2,
 14     x1.col3 = x2.col3,
 15     x1.action_flag = x2.new_flag
 16  when not matched then
 17     insert (pk_col,col2,col3,action_flag)
 18     values( x2.pk_col,x2.col2,x2.col3,x2.new_flag) ;

3 rows merged.

demo@PDB1> select * from test_stg2 order by pk_col;

    PK_COL CO CO A
---------- -- -- -
         1 A1 B1 A
         2 A2 B2 I
         3 A3 B3 A

demo@PDB1>

June 05, 2019 - 2:05 pm UTC

Yep, that looks like a more straightforward way of finding what to change.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

on Full outer joins

More to Explore

SQL

Using MERGE to update data 2 times

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

on Full outer joins

More to Explore

SQL