We have a data comparison project and I've been asked to provide support.
We have five (5) geographically separated Remedy servers that have menu tables which have "drifted" from the master dataset.
We have to analyze the tables of menu choices for all five and then use the data to sync up the core data. The plan was to print-out all records for the required tables from each site and manually (visually) review each record across the sites. I think a better way would be to get differences between the lists.
It's not too hard if it's just one field to compare back and forth -- just painful (one master, four children = four pairs of queries).
I can write outer joins on a specific field and test for IS NULL() to get records that are missing from the "child" table, but I only know how to do that for a SINGLE field. The problem I have will be with hierarchical fields.
What I HAVE constructed, that does work w/ hierarchical fields, is to concatenate the fields and do an exhaustive comparison (example for Master to Site1):
SELECT DISTINCT
"GNO to PAC" AS Label,
RFO1 + " / " + RFO2 AS RFO12
FROM GNOTable
WHERE RFO1 + " / " + RFO2 NOT IN
(SELECT DISTINCT RFO1 + " / " + RFO2
FROM PACTable)
with eight (8) queries built in pairs (GNO to PAC, PAC to GNO, etc.). The text field "Label" holds a string to designate which tables are being compared. After I tested each, I Unioned them together to get a very slow-running query to give me all combinations.
Using Left (or Right) outer joins (testing for IS NULL()) is much faster, but I only know how to test against a SINGLE field, and the fields have to keep their heirarchical relationship.
Does anybody know how to do this?
Or, does anyone have a better approach?
What is the "best"/"fastest" etc. way to compare back and forth?
We have five (5) geographically separated Remedy servers that have menu tables which have "drifted" from the master dataset.
We have to analyze the tables of menu choices for all five and then use the data to sync up the core data. The plan was to print-out all records for the required tables from each site and manually (visually) review each record across the sites. I think a better way would be to get differences between the lists.
It's not too hard if it's just one field to compare back and forth -- just painful (one master, four children = four pairs of queries).
I can write outer joins on a specific field and test for IS NULL() to get records that are missing from the "child" table, but I only know how to do that for a SINGLE field. The problem I have will be with hierarchical fields.
What I HAVE constructed, that does work w/ hierarchical fields, is to concatenate the fields and do an exhaustive comparison (example for Master to Site1):
SELECT DISTINCT
"GNO to PAC" AS Label,
RFO1 + " / " + RFO2 AS RFO12
FROM GNOTable
WHERE RFO1 + " / " + RFO2 NOT IN
(SELECT DISTINCT RFO1 + " / " + RFO2
FROM PACTable)
with eight (8) queries built in pairs (GNO to PAC, PAC to GNO, etc.). The text field "Label" holds a string to designate which tables are being compared. After I tested each, I Unioned them together to get a very slow-running query to give me all combinations.
Using Left (or Right) outer joins (testing for IS NULL()) is much faster, but I only know how to test against a SINGLE field, and the fields have to keep their heirarchical relationship.
Does anybody know how to do this?
Or, does anyone have a better approach?
What is the "best"/"fastest" etc. way to compare back and forth?
