Identify the columns where the value changed from previous version

Question

I have a table where values in certain columns can change. My requirement is to identify the items(id) where the value has changed. E.g.

Input:

ID  VALUE1  VALUE2  VALUE3
1    A       B       C
1    X       B       C
2    D       E       F
2    D       E       F
3    G       H       I
3    S       H       T

Output Needed:

ID  VALUE1  VALUE2  VALUE3
1   X       
3   S               T

I am using Oracle SQL. ANy help will be appreciated

You can have a look at - https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4007.htm — Sudipta Mondal, Jun 25 '18 at 09:16
It can be nullified. Even that should be counted as a change. — Shashi Shankar Singh, Jun 25 '18 at 10:35

Aleksej · Answer 1 · 2018-06-25T09:26:58.393

This could be a way, assuming that you need to order your records by some column (I added a row_num column just to explain):

select *
from (
    select ID, 
           case when lag(value1) over (partition by ID order by row_num) != value1 then value1 end as value1,
           case when lag(value2) over (partition by ID order by row_num) != value2 then value2 end as value2,
           case when lag(value3) over (partition by ID order by row_num) != value3 then value3 end as value3
    from yourTable
    )
where value1 is not null
   or value2 is not null
   or value3 is not null

This uses lag to get the value in the preceding row (ordered by row_num) for the same ID and then simply checks if there's at least one difference.

With your sample data, this

with yourTable(row_num, ID, VALUE1, VALUE2, VALUE3) as (
    select 1, 1, 'A', 'B', 'C' from dual union all
    select 2, 1, 'X', 'B', 'C' from dual union all
    select 3, 2, 'D', 'E', 'F' from dual union all
    select 4, 2, 'D', 'E', 'F' from dual union all
    select 5, 3, 'G', 'H', 'I' from dual union all
    select 6, 3, 'S', 'H', 'T' from dual 
)
select *
from (
    select ID, 
           case when lag(value1) over (partition by ID order by row_num) != value1 then value1 end as value1,
           case when lag(value2) over (partition by ID order by row_num) != value2 then value2 end as value2,
           case when lag(value3) over (partition by ID order by row_num) != value3 then value3 end as value3
    from yourTable
    )
where value1 is not null
   or value2 is not null
   or value3 is not null

gives

        ID VALUE1 VALUE2 VALUE3
---------- ------ ------ ------
         1 X                   
         3 S             T     

2 rows selected.

Thanks Aleksej.. Will try the solution provided and provide the feedback. — Shashi Shankar Singh, Jun 25 '18 at 10:36

score 0 · Answer 2 · answered Jun 27 '18 at 08:10

Your solution needs three parts.

You need to define an order for the changes. In your example it is "from top down to bottom". In your table, you'll have likely a date column or numerical id.
As @Aleksej suggests, you can then access the previous value with LAG(value) OVER (... ORDER BY ...)
You need a comparision function, which handles NULL values correctly. This is a bit painful, and there are more solutions to it, none of which is nice. I'd recommend DECODE(old_value, new_value, 0, 1)=1, see here for other examples.

I've added some extra rows to your table to test the changes involving NULL values:

CREATE TABLE mytable (id NUMBER, value1 VARCHAR2(1), value2 VARCHAR2(1), value3 VARCHAR2(1), t TIMESTAMP DEFAULT SYSTIMESTAMP);
INSERT INTO mytable (id,value1,value2,value3) VALUES (1, 'A','B','C');
INSERT INTO mytable (id,value1,value2,value3) VALUES (1, 'X','B','C');
INSERT INTO mytable (id,value1,value2,value3) VALUES (2, 'D','E','F');
INSERT INTO mytable (id,value1,value2,value3) VALUES (2, 'D','E','F');
INSERT INTO mytable (id,value1,value2,value3) VALUES (3, 'G','H','I');
INSERT INTO mytable (id,value1,value2,value3) VALUES (3, 'S','H','T');
INSERT INTO mytable (id,value1,value2,value3) VALUES (3, 'S','H',NULL);
INSERT INTO mytable (id,value1,value2,value3) VALUES (3, 'S','H',NULL);
INSERT INTO mytable (id,value1,value2,value3) VALUES (3, 'S','U','T');


SELECT ID,
       CASE WHEN value1_changed=1 THEN value1 END AS value1, 
       CASE WHEN value2_changed=1 THEN value2 END AS value2,
       CASE WHEN value3_changed=1 THEN value3 END AS value3,
       value1_changed,
       value2_changed,
       value3_changed
  FROM (
        SELECT id, value1, value2, value3,
               DECODE(value1, LAG(value1) OVER (PARTITION BY ID ORDER BY t), 0, 1) value1_changed,       
               DECODE(value2, LAG(value2) OVER (PARTITION BY ID ORDER BY t), 0, 1) value2_changed,
               DECODE(value3, LAG(value3) OVER (PARTITION BY ID ORDER BY t), 0, 1) value3_changed,
               row_number() OVER (PARTITION BY ID ORDER BY t) AS r, t
          FROM mytable
       ) 
 WHERE r > 1
   AND value1_changed + value2_changed + value3_changed >= 0;


ID  value1 value2 value3  changed1 changed2 changed3
 1  X                     1        0        0
 3  S             T       1        0        1
 3                        0        0        1
 3                U       0        0        1

Please not the 3rd line, when value3 changed from 'T' to NULL. It gets correctly reported, but only with it's new value NULL.

Identify the columns where the value changed from previous version

2 Answers2