Oracle Apex IG Query to compare 2 tables

Question

I need to write IG query to compare 2 tables with same structure but in different databases.

In local we have:

select emp_id,emp_name,emp_grade,emp_org,emp_unit from employees;

In the destination db we have:

select emp_id,emp_name,emp_grade,emp_org,emp_unit from employees@dest_db;

I need to display all rows with differences from employees@dest_db and also rows which are there in employees@dest_db but not employees.

I tried using case:

select emp_id,emp_name,emp_grade,emp_org,emp_unit,
case when a.emp_id=b.emp_id then 1 else 0 end empid_match ,
case when a.emp_name=b.emp_name then 1 else 0 end emp_name_match ,
case when a.emp_grade=b.emp_grade then 1 else 0 end emp_grade_match ,
case when a.emp_org=b.emp_org then 1 else 0 end emporg_match ,
case when a.emp_unit=b.emp_unit then 1 else 0 end emp_unit_match ,
 from employees@dest_db a, employees b 
where a.emp_id = b.emp_id and b.database='DEST_DB';

But this only returns rows with difference and highlights them. I have added conditions in IG for the empid_match etc values to highlight if 1.

But how do i modify the query to also return rows that exist in employees@dest_db and not employees?

Answer 1

how do i modify the query to also return rows that exist in employees@dest_db and not employees?

Looks like outer join problem. Modify FROM clause from this

from employees@dest_db a, employees b 
where a.emp_id = b.emp_id and b.database = 'DEST_DB'

to

from employees@dest_db a left join employees b on a.emp_id = b.emp_id
                                              and b.database = 'DEST_DB';
                         ---------            ---                             
                         this                 put condition into JOIN 

Or, if you prefer old Oracle's outer join syntax,

from employees@dest_db a, employees b 
where a.emp_id = b.emp_id (+) and b.database (+) = 'DEST_DB'
                          ---                ---
                          this               this