Oracle SQL


I need some help with writing a query to compare 2 same tables but from different environments. Basically we have a development and production environment. My task is to compare the tables for data between the development and production. I need the records to match between those tables and also see if the column value is same or different between them.

Here is what I have so far. I have attached sample table structure and expected outcome.

 I got those results from the attached query. One thing I need help is how to capture only 10 sample records for each of the column discrepancy found? For example if column "amqe_dept" from the attached sample has a discrepancy that is more than 10 records then I only want to see the first 10 records only that column. If "ampe_empe_num" has a discrepancy of 2 records then I want to see all 2 records. Basically, I want to see sample of discrepancies for each column in the table. How can I do that?  Please help

  --source data
  ROWNUM                                               rownumber
     WHEN t.t_serial_num IS NULL THEN 'S-T'
     WHEN s.serial_num IS NULL THEN 'T-S'
     ELSE 'BWD'
   END                                                 error
  ,'SRC'                                               table_type
  ,'DBRIDGE/AMQE'                                      table_name
    --target data     
  ,ROWNUM                                              t_rownumber
     WHEN t.t_serial_num IS NULL THEN 'S-T'
     WHEN s.serial_num IS NULL THEN 'T-S'
     ELSE 'BWD'
   END                                                 t_error
  ,'TGT'                                               t_table_type
 ,'TRAM_AMQE'                                          t_table_name
  --join source and target
  ON (s.amqe_rsn = t.t_amqe_rsn)
  -- S-T records 
  t.t_serial_num IS NULL
  -- T-S records
  OR s.serial_num IS NULL
  -- BWD records
    DECODE(s.amqe_dept, t.t_amqe_dept  ,''  ,'AMQE_DEPT:'||s.amqe_dept||':'||t.t_amqe_dept||chr(10) )
    ||DECODE(s.amqe_empe_num, t.t_amqe_empe_num  ,''  ,'AMQE_EMPE_NUM:'||s.amqe_empe_num||':'||t.t_amqe_empe_num||chr(10) )
ORDER BY error

Open in new window

Who is Participating?
slightwv (䄆 Netminder) Commented:
SQL Developer is supposed to compare two tables between different databases and sort of do what you want.  I've not personally used it for that so I have to go with the online blogs.

I you still want a script:
Can you change your requirements and get a count of the differences?

See if this will help:
drop table tab1 purge;
create table tab1(amqe_rsn number, serial_num varchar2(10), amqe_dept char(1), amqe_empe_num varchar2(5));
insert into tab1 values(1,'serial 1','A','A1');
insert into tab1 values(2,'serial 2','A','A2');
insert into tab1 values(3,'serial 3','B','A3');
insert into tab1 values(4,'serial 4','B','A4');

drop table tab2 purge;
create table tab2(amqe_rsn number, serial_num varchar2(10), amqe_dept char(1), amqe_empe_num varchar2(5));
insert into tab2 values(1,'serial 1','A','A1');
insert into tab2 values(2,'serial 2','B','A2');
insert into tab2 values(3,'serial 3','B','AAA');
insert into tab2 values(5,'serial 5','C','ZZZ');

select s_serial, t_serial, amqe_diff,amqe_diff_count, amqe_empe_num_diff, amqe_empe_num_diff_count 
from (
	select  s_serial,
		count(case when amqe_diff is not null then 1 end) amqe_diff_count,
		count(case when amqe_empe_num_diff is not null then 1 end) amqe_empe_num_diff_count
	from (
		select s.serial_num s_serial,
			t.serial_num t_serial,
			case when s.amqe_dept != t.amqe_dept then  s.amqe_dept || ':' || t.amqe_dept end amqe_diff,
			case when s.amqe_empe_num != t.amqe_empe_num then  s.amqe_empe_num || ':' || t.amqe_empe_num end amqe_empe_num_diff
  		tab1 s full outer join tab2 t on (s.amqe_rsn = t.amqe_rsn)
	group by  s_serial, t_serial, amqe_diff, amqe_empe_num_diff
amqe_diff_count > 0 or amqe_empe_num_diff_count > 0

Open in new window

Mark GeerlingsDatabase AdministratorCommented:
Basic Oracle SQL does not have a way to limit the number of rows returned to an arbitrary value (like 10).

Also, in Oracle queries, combining a select of "rownum" plus including an "order by" clause at the same level in a query is a contradiction.  The "rownum" value is applied *BEFORE* (not after) the records are put in order, so it is never a good idea in Oracle to combine these in one single-level query.

If you want to use a multi-level query, you can use "rownun" at the inner level and "order by" at the outer level, something like this:
select BWD, SRC, ..., rownum
  from ([your query, but without "rownum"]);

A PL\SQL procedure can give you the option to limit the number of rows returned by a cursor in the procedure, if you need that capability.
angel7170Author Commented:
Thank you! I am not sure how do I change this to PL/SQL. please help
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Mark GeerlingsDatabase AdministratorCommented:
PL\SQL is Oracle's proprietary programming language for stored procedures in Oracle databases.  This adds the ability to use: loops, conditional logic, control logic, etc. in addition to what SQL supports.  This is just one of the possible ways to accomplish what you are trying to do in Oracle, but it isn't the only way.  And it may not be the simplest or best option for you.

If you have no experience writing PL\SQL procedures in Oracle and if you have no other need for PL\SQL, then this may not be your best option.   However, if I was given the requirements you outlined in your question, I would use PL\SQL to solve this business problem.  But, then the report would not be directly produced by just one SQL query.  It would be written as a plain ASCII text (or *.html) file by a PL\SQL procedure to a directory on the database server, and it would need to be retrieved (or copied) from there to a place where your client PC could get to it.

This procedure could be scheduled to run automatically overnight, or each weekend, or at whatever frequency you need.  Or, it could be run interactively, if needed.

Can you give us some more information about the business need for this report (how often or at what frequency is it needed; do you have a reporting tool that could be used to help with this, or must it be done in SQL and/or PL\SQL)?
SujithData ArchitectCommented:
@angel7170 - as you have done a FULL OUTER JOIN using amqe_rsn; can we assume that is the key between the src and target tables? Having a key is quite crucial to solve this requirement. Otherwise defining mismatching rows itself becomes tedious.

Have a look at this example. I have created two datasets. Src and TGT tables combined have 10 records. Out of them 6 have differences in AMQE_DEPT, and 6 records have differences in AMQE_EMPE_NUM.

SQL> select  * from table_source;

---------- -------------------- -------------
       100 dept1                         1111
       200 dept2                         2222
       300 dept3                         3333
       400 dept4                         4444
       500 dept5                         5555
       600 dept6                         6666
       700 dept7                         7777
       800 dept8                         8888

8 rows selected.

SQL> select  * from table_target;

---------- -------------------- -------------
       300 dept3-mismatch                3333
       400 dept4-mismatch                4444
       500 dept5                         5555
       600 dept6                         6666
       700 dept7                     77770000
       800 dept8                      8888000
       900 dept9                         9999
      1000 dept10                       11111

Open in new window

This query will pull the records where either AMQE_DEPT or AMQE_EMPE_NUM have 4 differences.
SQL> with src_data as (
  2  select   ts.amqe_rsn ts_amqe_rsn, tt.amqe_rsn tt_amqe_rsn
  3          ,ts.amqe_dept ts_amqe_dept, tt.amqe_dept tt_amqe_dept
  4          ,case when NVL(ts.amqe_dept, '~')  <>  NVL(tt.amqe_dept, '~') then 'diff' else 'no diff' end amqe_dept_ind
  5          ,ts.amqe_empe_num ts_amqe_empe_num, tt.amqe_empe_num tt_amqe_empe_num
  6          ,case when NVL(ts.amqe_empe_num, 0)  <>  NVL(tt.amqe_empe_num, 0) then 'diff' else 'no diff' end amqe_empe_num_ind
  7  from    table_source ts full outer join table_target tt on (ts.amqe_rsn = tt.amqe_rsn)
  8  where   NVL(ts.amqe_dept, '~')  <>  NVL(tt.amqe_dept, '~')
  9      OR  NVL(ts.amqe_empe_num, 0)  <>  NVL(tt.amqe_empe_num, 0)
 10  ), diff_data as (
 11  select   ts_amqe_rsn, tt_amqe_rsn
 12          ,ts_amqe_dept, tt_amqe_dept, amqe_dept_ind
 13          ,ROW_NUMBER() over (PARTITION BY amqe_dept_ind ORDER BY 1 ) amqe_dept_rn
 14          ,ts_amqe_empe_num, tt_amqe_empe_num, amqe_empe_num_ind
 15          ,ROW_NUMBER() over (PARTITION BY amqe_empe_num_ind ORDER BY 1) amqe_empe_num_rn
 16  from    src_data
 17  )
 18  select   ts_amqe_rsn, tt_amqe_rsn
 19          ,ts_amqe_dept, tt_amqe_dept
 20          ,ts_amqe_empe_num, tt_amqe_empe_num
 21  from    diff_data
 22  where       (amqe_dept_ind = 'diff'     and amqe_dept_rn <= 4 )
 23          OR  (amqe_empe_num_ind = 'diff' and amqe_empe_num_rn    <= 4)
 24  order by 1
 25  ;

----------- ----------- -------------------- -------------------- ---------------- ----------------
        100             dept1                                                 1111
        200             dept2                                                 2222
        300         300 dept3                dept3-mismatch                   3333             3333
        400         400 dept4                dept4-mismatch                   4444             4444
        700         700 dept7                dept7                            7777         77770000
        800         800 dept8                dept8                            8888          8888000

6 rows selected.


Open in new window

SujithData ArchitectCommented:
A better way to see the differences would be - to select the differences column wise. You have better control over the output this way.
See an illustration here using the same dataset as above.

SQL> with src_data_amqe_dept as (
  2  select   ts.amqe_rsn ts_amqe_rsn, tt.amqe_rsn tt_amqe_rsn, 'amqe_dept' column_name
  3          ,ts.amqe_dept ts_amqe_dept, tt.amqe_dept tt_amqe_dept
  4          ,ROW_NUMBER() over (PARTITION BY 1 ORDER BY
  5                                  case when ts.amqe_dept is not null
  6                                          and tt.amqe_dept is not null then 2 else 1 end desc ) amqe_dept_rn
  7  from    table_source ts full outer join table_target tt on (ts.amqe_rsn = tt.amqe_rsn)
  8  where   NVL(ts.amqe_dept, '~')  <>  NVL(tt.amqe_dept, '~')
  9  ), src_data_amqe_empe_num as (
 10  select   ts.amqe_rsn ts_amqe_rsn, tt.amqe_rsn tt_amqe_rsn, 'ts_amqe_empe_num' column_name
 11          ,ts.amqe_empe_num ts_amqe_empe_num, tt.amqe_empe_num tt_amqe_empe_num
 12          ,ROW_NUMBER() over (PARTITION BY 1 ORDER BY
 13                                  case when ts.amqe_empe_num is not null
 14                                          and tt.amqe_empe_num is not null then 2 else 1 end desc ) amqe_empe_num_rn
 15  from    table_source ts full outer join table_target tt on (ts.amqe_rsn = tt.amqe_rsn)
 16  where   NVL(ts.amqe_empe_num, 0)  <>  NVL(tt.amqe_empe_num, 0)
 17  )
 18  select  ts_amqe_rsn, tt_amqe_rsn, column_name, to_char(ts_amqe_dept) src_value, to_char(tt_amqe_dept) tgt_value
 19  from    src_data_amqe_dept
 20  where   amqe_dept_rn <= 4
 21  union all
 22  select  ts_amqe_rsn, tt_amqe_rsn, column_name, to_char(ts_amqe_empe_num) src_value, to_char(tt_amqe_empe_num) tgt_value
 23  from    src_data_amqe_empe_num
 24  where   amqe_empe_num_rn <= 4
 25  ;

TS_AMQE_RSN TT_AMQE_RSN COLUMN_NAME      SRC_VALUE                                TGT_VALUE
----------- ----------- ---------------- ---------------------------------------- ----------------------------------------
        300         300 amqe_dept        dept3                                    dept3-mismatch
        400         400 amqe_dept        dept4                                    dept4-mismatch
                    900 amqe_dept                                                 dept9
                   1000 amqe_dept                                                 dept10
        700         700 ts_amqe_empe_num 7777                                     77770000
        800         800 ts_amqe_empe_num 8888                                     8888000
                    900 ts_amqe_empe_num                                          9999
                   1000 ts_amqe_empe_num                                          11111

8 rows selected.


Open in new window

Explaining the query a bit
src_data_amqe_dept - identifies the rows where amqe_dept has a difference. and assigns a row number to the differing records. Case statement in the order by gives less weightage to outer joined records.
src_data_amqe_empe_num - does the same thing to amqe_empe_num

and the union all picks up the first four records from each of the above datasets. You can change 4 to whatever number you like.
angel7170Author Commented:
Thank you all for your help!
@Sujith - Your first solution was helpful as it pretty much matched the requirement of my client. Thanks again
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.