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
