Compare Data from the Same Table in two Different Environments-蒲公英云

Compare Data from the Same Table in two Different Environments

小灰灰 2021-11-13 18:12 285阅读 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)