Checking whether any two of ten fields are different (not NULL or equal)

Question

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.

@mkRabbani Yes — actually in my case a composite id, but you can suppose a field unique_id is present. — Charles, Aug 14 '19 at 16:31
I have an idea but I'm not sure if it's possible and I cannot try by myself right now. You can write something like `v1 NOT IN (v2, v3, v4 ...) or v2 NOT IN (v1, v3, ...)` — Arnaud Claudel, Aug 14 '19 at 16:39
@ArnaudClaudel NOT IN is not [NULL safe](https://stackoverflow.com/a/51049469/5070879) — Lukasz Szozda, Aug 14 '19 at 16:42
I was thinking something like _unpivot_ (columns to rows), but not sure about how it could work out... https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot — O-9, Aug 14 '19 at 16:44
@Charles, in Arnaud's solution, it may be better to leave *all* fields in the IN clause, but on each line comment-out (using inline /* */ style) the field in the IN clause that matches the individual field being compared on that line - with appropriate formatting, this will create columns of identical field names, and will create a visual staircase effect on the commented-out fields, and will help quickly identify errors or deviations from the pattern when you are dealing (potentially) with many tens of fields. — Steve, Aug 14 '19 at 18:54

score 3 · Accepted Answer · answered Aug 14 '19 at 18:35

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

Gordon Linoff · Answer 2 · 2019-08-14T16:49:34.027

In Oracle 12c+, you can use a lateral join:

select t.*
from t cross join lateral
     (select count(distinct field) as cnt
      from (select t.field1 as field from dual union all
            select t.field2 as field from dual union all
            select t.field3 as field from dual union all
            select t.field4 as field from dual union all
            select t.field5 as field from dual union all
            select t.field6 as field from dual union all
            select t.field7 as field from dual union all
            select t.field8 as field from dual union all
            select t.field9 as field from dual union all
            select t.field10 as field from dual 
           ) x
     ) x
where cnt > 1;

This is painful in earlier versions.

Here is on approach that is not too dismal:

select t.*
from (select t.*,
             (field1 || ',' || field2 || ',' || . . . || field10) as fields
      from t
     ) t
where replace(replace(fields, regexp_substr(fields, '[^,]+', 1), ''), ',', '') is not null;

This assumes that the fields themselves do not contain commas; if so use a different separator.

The idea is to return some value; then replace that value and commas and see if you are left with a NULL/empty string.

score 0 · Answer 3 · answered Aug 14 '19 at 16:54

This following script will allow you to check if all 10 values are different or not. The row will only return if all 10 values are different-

You can check DEMO HERE

WITH CTE(id,v1,v2,v3,v4,v5,v6,v7,v8,v9,v10)
AS
(
    SELECT 1,10,20,30,505,50,60,70,80,90,100 FROM DUAL
),
CTE2 AS
(
    SELECT A.ID,A.Value, 
    DENSE_RANK() OVER (PARTITION BY ID ORDER BY VALUE ) AS D_RANK_NUM
    -- As you said you have composit unique column, 
    -- you can add those columns here for PARTITION BY
    -- I have used ID as sample Unique column
    FROM
    (
        SELECT *
        FROM   CTE
        UNPIVOT (value FOR V IN (v1,v2,v3,v4,v5,v6,v7,v8,v9,v10))
    )A
)

SELECT * FROM CTE WHERE ID IN(
    SELECT ID FROM CTE2 WHERE D_RANK_NUM = 10
)

Checking whether any two of ten fields are different (not NULL or equal)

3 Answers3