Compare two records and highlight the differences

0
votes

I have a oracle apex report which fetches two rows every time. I want to highlight all the columns where data is different in two rows. So when user looks at the comparison report he don't have to go through all the columns to identify where data has been changed.

I have tried to look at the apex features and some javascript code but was unable to do so reliably.

You can have a look at sample report here: https://apex.oracle.com/pls/apex/f?p=128616:8:109311280077805:::::

go to page "help me with comparison"

I want to highlight the benefit name column as data is different in benefit name column.

2
oracleoracle-apex

2 Answers

1
votes

if your table has id "test" you can try call this function on page load compareRows(document.getElementById('test'));

the function body:

function compareRows(table) {
    var row1,row2, rows = table.rows;
    var cell1,cell2;
    var rowText;
    row1=rows[1];
    row2=rows[2];
    cell1=row1.cells;
    cell2=row2.cells;
    for (var i=0; i<cell1.length; i++) {
    if(cell1[i].textContent != cell2[i].textContent){
    cell1[i].style.backgroundColor = "red";
    cell2[i].style.backgroundColor = "red";
    }
    }
    }

enter image description here

1
votes

You can use the analytic function "LAG" to reference the previous row in your resultset. So one possible solution is to (1) select the value of the current row and the value of the previous row, (2) compare the 2 columns and set a flag, only in row 2 because that is where you want to highlight, (3) use highlighting in apex to indicate which columns have different values. See example sql below for an example.

-- create tables
create table so_dummy_data (
    id                             number generated by default on null as identity  
                                   constraint so_dummy_data_id_pk primary key,
    name                           varchar2(100) not null,
    email                          varchar2(100) not null
)
;

-- load data

insert into so_dummy_data (
    id,
    name,
    email
) values (
    1,
    'John Doe',
    '[email protected]'
);

insert into so_dummy_data (
    id,
    name,
    email
) values (
    2,
    'John Doe',
    '[email protected]'
);

commit;


WITH old_and_new AS
(SELECT
                id,
                name,
                LAG(name,1)OVER(
                   ORDER BY
                    name
                )AS new_name,
                email,
                LAG(email,1)OVER(
                   ORDER BY
                    1
                )AS new_email,
                row_number() over (order by 1) rn
                FROM
                so_dummy_data
)
SELECT
  name,
  CASE
    WHEN rn = 1 THEN 'N'
    WHEN name = new_name THEN
      'N'
    ELSE
      'Y'
  END AS name_changed,
  email,
  CASE
    WHEN rn = 1 THEN 'N'
    WHEN email = new_email THEN
      'N'
    ELSE
      'Y' 
  END AS email_changed
FROM
old_and_new;