Forum Stats

  • 3,837,091 Users
  • 2,262,225 Discussions
  • 7,900,202 Comments

Discussions

Delta load logic using oracle merge/delete sql statement

user12251389
user12251389 Member Posts: 334 Blue Ribbon
edited Oct 20, 2021 3:38PM in SQL & PL/SQL

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.

 

Best Answer

«13

Answers

  • JonWat
    JonWat Member Posts: 550 Silver Badge

    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
    user12251389 Member Posts: 334 Blue Ribbon
    edited Jan 19, 2021 2:17PM

    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?


  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,212 Red Diamond
    edited Jan 19, 2021 3:09PM

    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
    JonWat Member Posts: 550 Silver Badge

    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
    Paulzip Member Posts: 8,718 Blue Diamond
    edited Jan 19, 2021 3:04PM

    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
    Frank Kulash Member, Moderator Posts: 42,212 Red Diamond

    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
  • user12251389
    user12251389 Member Posts: 334 Blue Ribbon
    edited Jan 19, 2021 3:29PM

    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
    user12251389 Member Posts: 334 Blue Ribbon

    @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
    user12251389 Member Posts: 334 Blue Ribbon

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

  • user12251389
    user12251389 Member Posts: 334 Blue Ribbon

    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 ?