4 Replies Latest reply: Nov 20, 2012 6:51 AM by 725469 RSS

    Split result of table data comparison to key(s), column, old, new each row

    725469
      Hi,

      I have two tables T1 and T2. T1 is the original backup snapshot for changed records from overnight batch in a big table and T2 is the overnight batch changed records. Both tables have similar number of rows (T2 might have more for newly inserted rows) and you can find out the differences by comparing these two according to action column in T2 (C - Update, A - Insert and D - Delete)

      My question is how to compare these two tables to generate somthing like the following. I can join these two tables to generate the diff but it is one row per account and the query is very ugly:)

      client_nbr branch_cd, account_cd, action column, old_value, new_value
      8888 123 45678 C account_clsfn_cd 004 005
      8888 123 45678 C buy_cd 98 99
      8888 012 34546 A sell_cd 12
      8888 321 98765 D dividend_cd 1

      I am using Oracle 10g so Unpivot cannot be used.

      Thanks,

      Steve

      CREATE TABLE T1
      (
      CLIENT_NBR CHAR(4 BYTE) NOT NULL,
      BRANCH_CD CHAR(3 BYTE) NOT NULL,
      ACCOUNT_CD CHAR(5 BYTE) NOT NULL,
      ACCOUNT_CLSFN_CD CHAR(3 BYTE),
      SELL_CD CHAR(2 BYTE),
      BUY_CD CHAR(2 BYTE),
      DIVIDEND_CD CHAR(1 BYTE),
      ACTION CHAR(1 BYTE) NOT NULL
      );



      CREATE TABLE T2
      (
      CLIENT_NBR CHAR(4 BYTE) NOT NULL,
      BRANCH_CD CHAR(3 BYTE) NOT NULL,
      ACCOUNT_CD CHAR(5 BYTE) NOT NULL,
      ACCOUNT_CLSFN_CD CHAR(3 BYTE),
      SELL_CD CHAR(2 BYTE),
      BUY_CD CHAR(2 BYTE),
      DIVIDEND_CD CHAR(1 BYTE),
      ACTION CHAR(1 BYTE) NOT NULL
      );


      insert into T1(client_nbr, branch_cd, account_cd, action, account_clsfn_cd, sell_cd, buy_cd, dividend_cd) values ('8888', '123', '45678', 'A', '004', null, '98', null);
      insert into T1(client_nbr, branch_cd, account_cd, action, account_clsfn_cd, sell_cd, buy_cd, dividend_cd) values ('8888', '321', '98765', 'A', null, null, null, '1');

      insert into T2(client_nbr, branch_cd, account_cd, action, account_clsfn_cd, sell_cd, buy_cd, dividend_cd) values ('8888', '321', '98765', 'D', null, null, null, null);
      insert into T2(client_nbr, branch_cd, account_cd, action, account_clsfn_cd, sell_cd, buy_cd, dividend_cd) values ('8888', '123', '45678', 'C', '005', null, '99', null);
      insert into T2(client_nbr, branch_cd, account_cd, action, account_clsfn_cd, sell_cd, buy_cd, dividend_cd) values ('8888', '012', '34546', 'A', null, '12', null, null);

      commit;



      select client_nbr, branch_cd, account_cd, action,
      case when n_account_clsfn_cd != o_account_clsfn_cd or (n_account_clsfn_cd is not null and o_account_clsfn_cd is null) or (n_account_clsfn_cd is null and o_account_clsfn_cd is not null) then 'account_clsfn_cd' end account_clsfn_cd,
      case when n_account_clsfn_cd != o_account_clsfn_cd or (n_account_clsfn_cd is not null and o_account_clsfn_cd is null) or (n_account_clsfn_cd is null and o_account_clsfn_cd is not null) then o_account_clsfn_cd end o_account_clsfn_cd,
      case when n_account_clsfn_cd != o_account_clsfn_cd or (n_account_clsfn_cd is not null and o_account_clsfn_cd is null) or (n_account_clsfn_cd is null and o_account_clsfn_cd is not null) then n_account_clsfn_cd end n_account_clsfn_cd,
      case when n_sell_cd != o_sell_cd or (n_sell_cd is not null and o_sell_cd is null) or (n_sell_cd is null and o_sell_cd is not null) then 'sell_cd' end sell_cd,
      case when n_sell_cd != o_sell_cd or (n_sell_cd is not null and o_sell_cd is null) or (n_sell_cd is null and o_sell_cd is not null) then o_sell_cd end o_sell_cd,
      case when n_sell_cd != o_sell_cd or (n_sell_cd is not null and o_sell_cd is null) or (n_sell_cd is null and o_sell_cd is not null) then n_sell_cd end n_sell_cd,
      case when n_buy_cd != o_buy_cd or (n_buy_cd is not null and o_buy_cd is null) or (n_buy_cd is null and o_buy_cd is not null) then 'buy_cd' end buy_cd,
      case when n_buy_cd != o_buy_cd or (n_buy_cd is not null and o_buy_cd is null) or (n_buy_cd is null and o_buy_cd is not null) then o_buy_cd end o_buy_cd,
      case when n_buy_cd != o_buy_cd or (n_buy_cd is not null and o_buy_cd is null) or (n_buy_cd is null and o_buy_cd is not null) then n_buy_cd end n_buy_cd,
      case when n_dividend_cd != o_dividend_cd or (n_dividend_cd is not null and o_dividend_cd is null) or (n_dividend_cd is null and o_dividend_cd is not null) then 'dividend_cd' end dividend_cd,
      case when n_dividend_cd != o_dividend_cd or (n_dividend_cd is not null and o_dividend_cd is null) or (n_dividend_cd is null and o_dividend_cd is not null) then o_dividend_cd end o_dividend_cd,
      case when n_dividend_cd != o_dividend_cd or (n_dividend_cd is not null and o_dividend_cd is null) or (n_dividend_cd is null and o_dividend_cd is not null) then n_dividend_cd end n_dividend_cd
      from
      (
      select n.client_nbr, n.branch_cd, n.account_cd, o.account_clsfn_cd o_account_clsfn_cd, n.account_clsfn_cd n_account_clsfn_cd , o.sell_cd o_sell_cd, n.sell_cd n_sell_cd, o.buy_cd o_buy_cd, n.buy_cd n_buy_cd, o.dividend_cd o_dividend_cd, n.dividend_cd n_dividend_cd, n.action from
      t1 o, t2 n
      where
      o.client_nbr(+) = n.client_nbr and o.branch_cd(+) = n.branch_cd and o.account_cd(+) = n.account_cd
      )
        • 1. Re: Split result of table data comparison to key(s), column, old, new each row
          odie_63
          Hi,
          I am using Oracle 10g so Unpivot cannot be used.
          Well, luckily we can perform unpivot operations without the UNPIVOT operator.

          For example :
          SQL> with column_list as (
            2    select cast(column_value as varchar2(30)) column_name
            3    from table(sys.odcivarchar2list('ACCOUNT_CLSFN_CD', 'SELL_CD', 'BUY_CD', 'DIVIDEND_CD'))
            4  ),
            5  unpivot_t1 as (
            6    select client_nbr, branch_cd, account_cd, column_name
            7         , case column_name
            8                when 'ACCOUNT_CLSFN_CD' then account_clsfn_cd
            9                when 'SELL_CD' then sell_cd
           10                when 'BUY_CD' then buy_cd
           11                when 'DIVIDEND_CD' then dividend_cd
           12           end val
           13    from t1
           14         cross join column_list
           15  ),
           16  unpivot_t2 as (
           17    select client_nbr, branch_cd, account_cd, action, column_name
           18         , case column_name
           19                when 'ACCOUNT_CLSFN_CD' then account_clsfn_cd
           20                when 'SELL_CD' then sell_cd
           21                when 'BUY_CD' then buy_cd
           22                when 'DIVIDEND_CD' then dividend_cd
           23           end val
           24    from t2
           25         cross join column_list
           26  )
           27  select client_nbr, branch_cd, account_cd, column_name
           28       , up2.action, up1.val as old_val, up2.val as new_val
           29  from unpivot_t1 up1
           30       full outer join unpivot_t2 up2
           31                 using (client_nbr, branch_cd, account_cd, column_name)
           32  where up1.val is not null
           33     or up2.val is not null
           34  ;
           
          CLIENT_NBR BRANCH_CD ACCOUNT_CD COLUMN_NAME                    ACTION OLD_VAL NEW_VAL
          ---------- --------- ---------- ------------------------------ ------ ------- -------
          8888       321       98765      DIVIDEND_CD                    D      1       
          8888       123       45678      ACCOUNT_CLSFN_CD               C      004     005
          8888       123       45678      BUY_CD                         C      98      99
          8888       012       34546      SELL_CD                        A              12
           
          or,
          SQL> with column_list as (
            2    select cast(column_value as varchar2(30)) column_name
            3    from table(sys.odcivarchar2list('ACCOUNT_CLSFN_CD', 'SELL_CD', 'BUY_CD', 'DIVIDEND_CD'))
            4  ),
            5  unpivot_t as (
            6    select indic, client_nbr, branch_cd, account_cd, action, column_name
            7         , case column_name
            8                when 'ACCOUNT_CLSFN_CD' then account_clsfn_cd
            9                when 'SELL_CD' then sell_cd
           10                when 'BUY_CD' then buy_cd
           11                when 'DIVIDEND_CD' then dividend_cd
           12           end val
           13    from (
           14      select '1' as indic, t1.* from t1
           15      union all
           16      select '2', t2.* from t2
           17    )
           18    cross join column_list
           19  )
           20  select client_nbr, branch_cd, account_cd, column_name
           21       , max(case when indic = 2 then action end) as action
           22       , max(case when indic = 1 then val end) as old_val
           23       , max(case when indic = 2 then val end) as new_val
           24  from unpivot_t up
           25  group by client_nbr, branch_cd, account_cd, column_name
           26  having max(case when indic = 1 then val end) is not null
           27      or max(case when indic = 2 then val end) is not null
           28  ;
           
          CLIENT_NBR BRANCH_CD ACCOUNT_CD COLUMN_NAME                    ACTION OLD_VAL NEW_VAL
          ---------- --------- ---------- ------------------------------ ------ ------- -------
          8888       123       45678      ACCOUNT_CLSFN_CD               C      004     005
          8888       123       45678      BUY_CD                         C      98      99
          8888       012       34546      SELL_CD                        A              12
          8888       321       98765      DIVIDEND_CD                    D      1       
           
          Edited by: odie_63 on 9 nov. 2012 10:40
          • 2. Re: Split result of table data comparison to key(s), column, old, new each row
            725469
            odie,

            Thanks a lot for your help. Both queries work gracefully but I need some time to digest:) WOW!!!!

            How about adding some other columns that are not varchar2? Converting non-string data to varchar2 in the case/when section? I changed the table structure a little bit to add two more columns for number and date.

            CREATE TABLE T1
            (
            CLIENT_NBR CHAR(4 BYTE) NOT NULL,
            BRANCH_CD CHAR(3 BYTE) NOT NULL,
            ACCOUNT_CD CHAR(5 BYTE) NOT NULL,
            ACCOUNT_CLSFN_CD CHAR(3 BYTE),
            SELL_CD CHAR(2 BYTE),
            BUY_CD CHAR(2 BYTE),
            DIVIDEND_CD CHAR(1 BYTE),
            FEE NUMBER(10,2),
            TRANSFER_DT DATE,
            ACTION CHAR(1 BYTE) NOT NULL
            );


            CREATE TABLE T2
            (
            CLIENT_NBR CHAR(4 BYTE) NOT NULL,
            BRANCH_CD CHAR(3 BYTE) NOT NULL,
            ACCOUNT_CD CHAR(5 BYTE) NOT NULL,
            ACCOUNT_CLSFN_CD CHAR(3 BYTE),
            SELL_CD CHAR(2 BYTE),
            BUY_CD CHAR(2 BYTE),
            DIVIDEND_CD CHAR(1 BYTE),
            FEE NUMBER(10,2),
            TRANSFER_DT DATE,
            ACTION CHAR(1 BYTE) NOT NULL
            );


            insert into T1(client_nbr, branch_cd, account_cd, action, account_clsfn_cd, sell_cd, buy_cd, dividend_cd, fee, transfer_dt) values ('8888', '123', '45678', 'A', '004', null, '98', null, 100.00, sysdate);
            insert into T1(client_nbr, branch_cd, account_cd, action, account_clsfn_cd, sell_cd, buy_cd, dividend_cd, fee, transfer_dt) values ('8888', '321', '98765', 'A', null, null, null, '1', 132.20, sysdate);

            insert into T2(client_nbr, branch_cd, account_cd, action, account_clsfn_cd, sell_cd, buy_cd, dividend_cd, fee, transfer_dt) values ('8888', '321', '98765', 'D', null, null, null, null, null, null);
            insert into T2(client_nbr, branch_cd, account_cd, action, account_clsfn_cd, sell_cd, buy_cd, dividend_cd, fee, transfer_dt) values ('8888', '123', '45678', 'C', '005', null, '99', null, 1200.00, sysdate);
            insert into T2(client_nbr, branch_cd, account_cd, action, account_clsfn_cd, sell_cd, buy_cd, dividend_cd, fee, transfer_dt) values ('8888', '012', '34546', 'A', null, '12', null, null, 299.00, sysdate);

            commit;


            Thanks,

            Steve

            Edited by: hsteve on Nov 9, 2012 12:16 PM
            • 3. Re: Split result of table data comparison to key(s), column, old, new each row
              odie_63
              How about adding some other columns that are not varchar2? Converting non-string data to varchar2 in the case/when section?
              It doesn't make a big difference. What did you try?
              Just add the new columns in the list and as you said convert them in the CASE statement :
              SQL> with column_list as (
                2    select cast(column_value as varchar2(30)) column_name
                3    from table(sys.odcivarchar2list('ACCOUNT_CLSFN_CD', 'SELL_CD', 'BUY_CD', 'DIVIDEND_CD', 'FEE', 'TRANSFER_DT'))
                4  ),
                5  unpivot_t1 as (
                6    select client_nbr, branch_cd, account_cd, column_name
                7         , case column_name
                8                when 'ACCOUNT_CLSFN_CD' then account_clsfn_cd
                9                when 'SELL_CD' then sell_cd
               10                when 'BUY_CD' then buy_cd
               11                when 'DIVIDEND_CD' then dividend_cd
               12                when 'FEE' then to_char(fee)
               13                when 'TRANSFER_DT' then to_char(transfer_dt, 'DD/MM/YYYY HH24:MI:SS')
               14           end val
               15    from t1
               16         cross join column_list
               17  ),
               18  unpivot_t2 as (
               19    select client_nbr, branch_cd, account_cd, action, column_name
               20         , case column_name
               21                when 'ACCOUNT_CLSFN_CD' then account_clsfn_cd
               22                when 'SELL_CD' then sell_cd
               23                when 'BUY_CD' then buy_cd
               24                when 'DIVIDEND_CD' then dividend_cd
               25                when 'FEE' then to_char(fee)
               26                when 'TRANSFER_DT' then to_char(transfer_dt, 'DD/MM/YYYY HH24:MI:SS')
               27           end val
               28    from t2
               29         cross join column_list
               30  )
               31  select client_nbr, branch_cd, account_cd, column_name
               32       , up2.action, up1.val as old_val, up2.val as new_val
               33  from unpivot_t2 up2
               34       left outer join unpivot_t1 up1
               35                 using (client_nbr, branch_cd, account_cd, column_name)
               36  where up1.val is not null
               37     or up2.val is not null
               38  ;
               
              CLIENT_NBR BRANCH_CD ACCOUNT_CD COLUMN_NAME                    ACTION OLD_VAL                                  NEW_VAL
              ---------- --------- ---------- ------------------------------ ------ ---------------------------------------- ----------------------------------------
              8888       321       98765      DIVIDEND_CD                    D      1                                        
              8888       321       98765      FEE                            D      132,2                                    
              8888       321       98765      TRANSFER_DT                    D      10/11/2012 11:27:55                      
              8888       123       45678      ACCOUNT_CLSFN_CD               C      004                                      005
              8888       123       45678      BUY_CD                         C      98                                       99
              8888       123       45678      FEE                            C      100                                      1200
              8888       123       45678      TRANSFER_DT                    C      10/11/2012 11:27:54                      10/11/2012 11:27:57
              8888       012       34546      SELL_CD                        A                                               12
              8888       012       34546      FEE                            A                                               299
              8888       012       34546      TRANSFER_DT                    A                                               10/11/2012 11:27:58
               
              10 rows selected
               
              (NB: after reading again the scenario, I switched back to a simple LEFT OUTER JOIN UP2-->UP1)

              Edited by: odie_63 on 10 nov. 2012 11:41
              • 4. Re: Split result of table data comparison to key(s), column, old, new each row
                725469
                Thanks a lot, Odie. This is really neat! I also change the where clause to "where (up1.val is not null and up2.val is null) or (up1.val is null and up2.val is not null) or (up1.val <> up2.val)" to reflect the difference between old and new and tried it on a big table which has 160 columns and 1 million rows. The performance is not very good at the moment and I am working on the tuning. One thing I can do is to compare changed row only with a temp table populated by Informatica.

                Steve