This discussion is archived
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 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points