I have a SQL query containing (among others) ten fields, call them v1, ..., v10, which I would like to test. The expected situation is that most are NULL and the remaining values are all equal. The interesting (error?) case I’m searching for is when there are at least two non-NULL values which are unequal.
Is there some better way to do this than
v1 != v2 or v1 != v3 or ... v8 != v9 or v8 != v10 or v9 != v10
with binomial(10, 2) = 45 conditions in total?
Aside from being inelegant it seems brittle —and having just debugged an issue where one variable in a long list had a typo, not merely an academic concern. But if that’s the only way it’s doable... though if the list expands to 20 it’s much less nice.
UNPIVOT the columns to rows and then GROUP BY your primary key and COUNT the DISTINCT values in the unpivoted columns to see if there is more than one unique value:
Oracle 11 Setup:
CREATE TABLE table_name ( id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10 ) AS
SELECT 1, 'A', 'A', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM DUAL UNION ALL
SELECT 2, 'A', NULL, 'B', NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM DUAL UNION ALL
SELECT 3, 'A', NULL, 'A', 'A', NULL, 'A', 'A', 'A', 'A', 'A' FROM DUAL UNION ALL
SELECT 4, 'A', NULL, 'A', 'A', 'B', NULL, NULL, NULL, NULL, NULL FROM DUAL;
Query:
SELECT id
FROM table_name
UNPIVOT ( value FOR name IN ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10 ) )
GROUP BY id
HAVING COUNT( DISTINCT value ) > 1
Output:
| ID | | -: | | 2 | | 4 |
db<>fiddle here