Delta load logic using oracle merge/delete sql statement

user12251389

I have table TEST_RUA where i am importing data daily. I am using Truncate and Insert logic for this table. Now i have another table TEST_RUA_MER same like table TEST_RUA where i want to apply Delta load logic. There is always unique combination of column values ID_LL, ID_UU, TKR in both table.

Frank Kulash

Hi,

I have edited the question and in the expected output ...

Please don't ever change your messages after you post them: it makes the thread hard to follow and it makes your changes easy to miss. Make all corrections and additions in a new message. (It's okay to add something like "EDIT: new sample data and results are posted below", but that's the only kind of change you should make to an existing message.)

Can you please update your query here accordingly. 

You just need to change the conditions in the ON clause, like this:

MERGE INTO test_rua_mer dst
USING (
      SELECT  id_ll, id_uu, tkr
      ,	   tr.class, tr.name -- Plus any additional columns
	  ,     CASE
	         WHEN tr.ROWID IS NULL
			 OR  (  DECODE (tr.class, trm.class, 'SAME') = 'SAME'
			    AND DECODE (tr.name, trm.name, 'SAME') = 'SAME'
              -- AND ... -- the same for any additional columns
				)
			 THEN 'DELETE'
	       END  AS delete_flag
      FROM   test_rua   tr
      FULL JOIN test_rua_mer trm USING (id_ll, id_uu, tkr)
    )         src
ON (  DECODE (src.id_ll, dst.id_ll, 'SAME') = 'SAME'
  AND DECODE (src.id_uu, dst.id_uu, 'SAME') = 'SAME'
  AND DECODE (src.tkr,   dst.tkr,   'SAME') = 'SAME'
  )
WHEN NOT MATCHED
THEN INSERT (dst.class, dst.id_ll, dst.id_uu, dst.tkr, dst.name) -- plus any additional columns
     VALUES (src.class, src.id_ll, src.id_uu, src.tkr, src.name) -- plus any additional columns
WHEN MATCHED THEN UPDATE
  SET dst.class = src.class
  ,   dst.name  = src.name
-- ,  ...  -- the same for any additional column
DELETE WHERE src.delete_flag = 'DELETE'
;

Aside from the three lines in the ON clause and comments, this is exactly what I posted in my previous message.

 I actually dont understand the below part :

CASE

   WHEN tr.ROWID IS NULL

OR ( DECODE (tr.class, trm.class, 'SAME') = 'SAME'

 AND DECODE (tr.name, trm.name, 'SAME') = 'SAME'

)

THEN 'DELETE'

  END AS delete_flag

This defines a column in dst called delete_flag, which will have the value 'DELETE' when either

• a row in test_rua_mer has no match in test_rua (that's the only time when tr.ROWID will be NULL, assuming test_rua is a real table), OR
• both the class and name columns are the same in both tables (where both being NULL counts as the same, but exactly one being NULL does not)

These are the conditions for an existing row in test_rua_mer to be deleted. This value will be used in the DELETE WHERE clause, which is your next question.

And also this part:

DELETE WHERE src.delete_flag = 'DELETE'

This tells MERGE to DELETE any row in the destination table that had a matching row in the source "table" with delete_flag equal to the six-character string 'DELETE'.

If i add more fields in table where the field value might be null then it should not be problem with the query or do i need to handle it separately and how. ? I think if we just add those fields in Decode part will be sufficient ?

If you have more columns like class and name, then change every place in the query that mentions class and name to mention the additional columns the same way. See the comments in the code above.

JonWat

The merge below should do what you want. In the query for the merge the MINUS subtracts out all the rows that are unchanged from those in test_rua_mer. That's a fairly expensive operation: it has to compare all of the columns in one table with all of the columns in another, so if you had a cheaper way (like an update date or soemthing) to determine if the test_rua rows should be treated as changed, that would probably be better.

merge into test_rua_mer t USING   

  (select *

  from test_rua

  MINUS

  select *

  from test_rua_mer

  ) q

on (t.id_ll= q.id_ll and t.id_uu = q.id_uu and t.tkr = q.tkr)

when matched then update set class = q.class,name = q.name

when not matched then insert values(q.class,q.id_ll,q.id_uu,q.tkr,q.name);

user12251389

But how do i get only two rows at the end as mentioned in the expected output because this is what i need for select statement at the end...can we create view to filter out the data or i think we might need delete to maintain the table TEST_RUA_MER ? Because for example now during next import in TEST_RUA table if row 'Bond', 'BERF', 'GV9999B12M1', 'TKXX', 'TES_RES' does not exist but its already there in table TEST_RUA_MER then how can we filter this out from table TEST_RUA_MER as Merge will not be sufficient in this case ? Also we only need updated rows at the end in table TEST_RUA_MER for example mentioned in the expected output as Bond  BREG  TV9999B12M4 CVKR  FRTG_OP . How do we get that in select statment?

https://community.oracle.com/tech/developers/discussion/comment/16781846#Comment_16781846

Frank Kulash

Hi, @user12251389

Here's one way to do it:

MERGE INTO test_rua_mer dst
USING (
      SELECT  id_ll, id_uu, tkr
      ,	   tr.class, tr.name
      ,    CASE
	           WHEN tr.ROWID IS NULL
			 OR  (  LNNVL (tr.class <> trm.class)
			     AND LNNVL (tr.name <> trm.name)
			     )
		   THEN 'DELETE'
	   END  AS delete_flag
      FROM      test_rua     tr
      FULL JOIN test_rua_mer trm  USING (id_ll, id_uu, tkr)
    )         src
ON (  src.id_ll = dst.id_ll
   AND src.id_uu = dst.id_uu
   AND src.tkr   = dst.tkr
   )
WHEN NOT MATCHED
THEN INSERT (dst.class, dst.id_ll, dst.id_uu, dst.tkr, dst.name)
     VALUES (src.class, src.id_ll, src.id_uu, src.tkr, src.name)
WHEN MATCHED THEN UPDATE
  SET dst.class = src.class
  ,   dst.name  = src.name
DELETE WHERE src.delete_flag = 'DELETE'
;

This will update (rather than delete) when one table has a value in either class or name, and the other table has a matching row with NULL in the same column. (I assume that's what you mean by "when there is change".) y doesn't do whaT YOU WANT

EDIT: This may not do what you want when class and/or name is NULL. See my next message below.

By the way, CLASS and NAME are Oracle keywords. Using them as column names can cause problems.

JonWat

I think I understand what you are asking, but maybe I'm not.

In your example you have four existing rows in test_rua_mer and your process should add two rows. I think what you want to do is export just those two rows you added somewhere else, but the next time you run the process, those two rows are just part of the "existing" test_rua_mer, the same as the four rows are currently, and you won;t want to print/export them.

So I think what you need to do is add a "flag" column to test_rua_mer that says: this is a new row for printing, and when you print you reset that to null or zero to indicate it is not new any more.

Paulzip

You can determine your actions with the following query, and plug that into the merge or update statements.

This is based on a template approach by Stew Ashton, he has a package for doing delta changes on his blog.

select CLASS, ID_LL, ID_UU, TKR,
    count(*) over (partition by ID_LL, ID_UU, TKR) - sum(Z##CNT) Z##IUD_FLAG
    /* Z##IUD_FLAG
      0 : Insert. In source, not in destination
      1 : Update. New, from source to destination
      2 : Delete. Not in source, in destination
      3 : Update. Old from destination to source (generally not used)
    */     
from (
 select CLASS, ID_LL, ID_UU, TKR, -1 Z##CNT -- Target
 from TEST_RUA_MER
 union all
 select CLASS, ID_LL, ID_UU, TKR, 1 Z##CNT -- Source
 from TEST_RUA n  
)
group by CLASS, ID_LL, ID_UU, TKR

Frank Kulash

Hi,

I just tested with some NULL data. What do you want with this:

INSERT INTO TEST_RUA     VALUES (NULL, 'F1', 'F1', 'F1', NULL);
INSERT INTO TEST_RUA_MER VALUES (NULL, 'F1', 'F1', 'F1', NULL);
INSERT INTO TEST_RUA     VALUES (NULL, 'F2', 'F2', 'F2', 'A');
INSERT INTO TEST_RUA_MET VALUES (NULL, 'F2', 'F2', 'F2' NULL);

? If you want the row in test_rua_mer with id_ll='F1' to be deleted (because there was no change in the class and name columns) but you want the row in test_rua_mer with id_ll='F2' to be updated (because name changed from NULL to 'A') then do this:

MERGE INTO test_rua_mer dst
USING (
      SELECT  id_ll, id_uu, tkr
      ,	   tr.class, tr.name
	  ,     CASE
	         WHEN tr.ROWID IS NULL
			 OR  (  DECODE (tr.class, trm.class, 'SAME') = 'SAME'
			    AND DECODE (tr.name, trm.name, 'SAME') = 'SAME'
				)
			 THEN 'DELETE'
	       END  AS delete_flag
      FROM   test_rua   tr
      FULL JOIN test_rua_mer trm USING (id_ll, id_uu, tkr)
    )         src
ON (  src.id_ll = dst.id_ll
  AND src.id_uu = dst.id_uu
  AND src.tkr  = dst.tkr
  )
WHEN NOT MATCHED
THEN INSERT (dst.class, dst.id_ll, dst.id_uu, dst.tkr, dst.name)
   VALUES (src.class, src.id_ll, src.id_uu, src.tkr, src.name)
WHEN MATCHED THEN UPDATE
  SET dst.class = src.class
  ,  dst.name  = src.name
DELETE WHERE src.delete_flag = 'DELETE'
;

This is the same as the MERGE I posted earlier, except the two lines that used LNNVL to handle NULL have been changed to use DECODE instead.

The two statements behave the same when class and name are not NULL.

user12251389

https://community.oracle.com/tech/developers/discussion/comment/16781853#Comment_16781853

Thanks its working the way expected.. In the On clause you didnt check for null ...because sometime any of the value from id_ll or id_uu, tkr might be null. I really

I really dont know how this Merge is deleting/updating the data from table TEST_RUA_MER can you please explain ? Because if i run merge many times then i get different output in table TEST_RUA_MER see https://dbfiddle.uk/?rdbms=oracle_18&fiddle=d6cc6fe1f57ca3368057f99db236c3b2 So it looks like i dont need any Delete statement here ...

Last point EDIT: This may not do what you want when class and/or name is NULL. Sorry i dont understand here. Actually this column and values are coming from flat file so i need to keep this columns. Do you think its problem if they are null ? I just tested and you are right its failing somehow when the value is null for Class or Name field. Is there any solution on this...I can remove field Name but i need Class field in table..

user12251389

https://community.oracle.com/tech/developers/discussion/comment/16781860#Comment_16781860

@Paulzip i think for this we need PL/SQL package to trigger Insert or Update or Delete statament unfortuantely i dont want to create Pl/SQL package

user12251389

https://community.oracle.com/tech/developers/discussion/comment/16781855#Comment_16781855

Yes with flag also we can do this...i can add new field but will it be possible then with merge statment ?

user12251389

https://community.oracle.com/tech/developers/discussion/comment/16781863#Comment_16781863

Will it be problem if in Update clause i also use condition as dst.id_uu = src.id_uu , dst.id_ll = src.id_ll , dst.id_tkr = src.id_tkr ?