-
Union and Minus equivalents
Hi,
I need to perform a quality control check between an original report and the same report produced by a new process. Specifically i want to check that both versions of the report are identical across all columns and rows. The original report is held in a table "OR1" and the new report is in table "NR1".
In Oracle I would use:
(SELECT * -- list all rows in original report but not in new report
FROM OR1
MINUS
SELECT *
FROM NR1)
UNION ALL -- appended to
(SELECT * -- all rows in new report but not in original report
FROM NR1
MINUS
SELECT *
FROM OR1)
There will be zero rows output if both tables are identical.
How do I perform this check using Access 2002? (Please note that I need to check all 50 columns and not just the primary key.)
-
Found the answer to my own question on "Jeff's sql server blog":
SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3 ...
FROM
(
SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...
FROM A
UNION ALL
SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...
FROM B
) tmp
GROUP BY ID, COL1, COL2, COL3 ...
HAVING COUNT(*) = 1
ORDER BY ID
The inner "tmp" query needs to be handled as its own query.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|