Compare Data from the Same Table in two Different Environments

小灰灰 2021-11-13 18:12 197阅读 0赞

The Oracle SQL below compares table1 that has 2 key fields and 3 regular fields.

Note: For the SQL below to work, your password needs to be the same in both environments. If not, then  
a connection using the below will not be possible.

FROM table1 tst, table1@dev\_database dev

*-- Compare data from the same table in two different environments*  
**SELECT**   tst.fieldkey1, tst.fieldkey2,  
          (CASE tst.field1  
             WHEN dev.field1  
                THEN 'Match'  
             ELSE     'field1 in DEV is '  
                  || dev.field1  
                  || '. field1 in TEST is '  
                  || tst.field1  
          END  
          ) **AS** field1,  
          (CASE tst.field2  
             WHEN dev.field2  
                THEN 'Match'  
             ELSE     'field2 in DEV is '  
                  || dev.field2  
                  || '.  field2 in TEST is '  
                  || tst.field2  
          END  
          ) **AS** field2,  
          (CASE tst.field3  
             WHEN dev.field3  
                THEN 'Match'  
             ELSE     'field3 in DEV is '  
                  || dev.field3  
                  || '.  field3 in TEST is '  
                  || tst.field3  
          END  
          ) **AS** field3  
    **FROM** table1 tst, table1@dev\_database dev  
    **WHERE** tst.fieldkey1 = dev.fieldkey1  
      **AND** tst.fieldkey2 = dev.fieldkey2  
      **AND** (   tst.field1 <> dev.field1  
          **OR** tst.field2 <> dev.field2  
          **OR** tst.field3 <> dev.field3  
          )  
**ORDER** **BY** tst.fieldkey1, tst.fieldkey2;

OK, feel better? Another one for free!

SELECT   oprid1, run\_cntl\_id1

FROM (SELECT \*

FROM (SELECT oprid AS oprid1, run\_cntl\_id AS run\_cntl\_id1

FROM ps\_fas\_run\_ap735

UNION ALL

SELECT oprid AS oprid1, run\_cntl\_id AS run\_cntl\_id1

FROM ps\_fas\_run\_ap735@fsdev) a)

GROUP BY oprid1, run\_cntl\_id1

HAVING COUNT (\*) = 1

转载于:https://www.cnblogs.com/GoDevil/archive/2008/08/08/1263801.html