Calling all experts! I need your help!
I need to create conditional formatting (like highlighting) for multiple field values that do not match in my report that is based on a Union All query.
First, you might need to know that my Union All query (detail below) returns all rows in tables A and B that do not completely match all columns in either, and all rows in either table that do not exist in the other table, including nulls. Rows that match completely in both tables, are not returned.
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
In my report example below, the words “POLICY” and “ADMIN” would be highlighted in the first two rows. In rows 3 and 4, “CITY COUNCIL” and the null value would highlight, and in rows 5 and 6 “CITIZEN FEEDBACK” and “AGENDAS” would highlight.
Table \ ID \ COL1 \ COL2 \ COL3
A \ 1 \ POLICY \ CITY COUNCIL \ \
B \ 1 \ ADMIN \ CITY COUNCIL \ \
A \ 2 \ POLICY \ CITY COUNCIL \ \
B \ 2 \ POLICY \ \ \
A \ 3 \ POLICY \ CITY COUNCIL \ CITIZEN FEEDBACK
B \ 3 \ POLICY \ CITY COUNCIL \ AGENDAS
Since I have over 60,000 records to proof, I’m hoping someone will have a solution for me.
Thank you!
dawnit
I need to create conditional formatting (like highlighting) for multiple field values that do not match in my report that is based on a Union All query.
First, you might need to know that my Union All query (detail below) returns all rows in tables A and B that do not completely match all columns in either, and all rows in either table that do not exist in the other table, including nulls. Rows that match completely in both tables, are not returned.
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
In my report example below, the words “POLICY” and “ADMIN” would be highlighted in the first two rows. In rows 3 and 4, “CITY COUNCIL” and the null value would highlight, and in rows 5 and 6 “CITIZEN FEEDBACK” and “AGENDAS” would highlight.
Table \ ID \ COL1 \ COL2 \ COL3
A \ 1 \ POLICY \ CITY COUNCIL \ \
B \ 1 \ ADMIN \ CITY COUNCIL \ \
A \ 2 \ POLICY \ CITY COUNCIL \ \
B \ 2 \ POLICY \ \ \
A \ 3 \ POLICY \ CITY COUNCIL \ CITIZEN FEEDBACK
B \ 3 \ POLICY \ CITY COUNCIL \ AGENDAS
Since I have over 60,000 records to proof, I’m hoping someone will have a solution for me.
Thank you!
dawnit
