Check for data alignment in multiple columns, but don't treat NULL as being misaligned-CodePudding

I have three columns of data that I would like to use ORACLE SQL to check data-alignment for, but with the specific condition that NULL entries will be allowed within the three columns, but I would like NULL entries to be completely ignored in terms of the alignment checks, with only an ERROR condition being reported where there is a misalignment between NON NULL entries...

Some examples -

Col_1   Col_2    Col_3   Alignment
ABC     ABC      ABC     OK
ABC     ABC      NULL    OK
ABC     NULL     NULL    OK
NULL    ABC      ABC     OK
NULL    ABC      NULL    OK
NULL    NULL     NULL    OK
ABC     XYZ      ABC     ERROR
XYZ     XYZ      ABC     ERROR
ABC     NULL     XYZ     ERROR
XYZ     ABC      ABC     ERROR
NULL    NULL     XYZ     OK
XYZ     XYZ      NULL    OK
NULL    NULL     XYZ     OK

Given the combinations where NULL entries, pairs of NULL entries, or triple-entries of NULL data might be found, I can see that a large combination of CASE WHEN might be used to crudely generate the 4th alignment column, but I was hoping there might be an ORACLE SQL process available that could do away with a huge number of CASE WHEN statements, given that I only really want to check data-alignment where 'some data' exists in any of the three-column combinations...

Any help with the above would be greatly appreciated.

Kind Regards.

CodePudding user response：

Here's one option; this is SQL*Plus so I set it to display literally NULL when there's no value in that column (so that it looks as your example).

SQL> set null NULL
SQL>
SQL> select col1, col2, col3,
  2    case when (col1 = col2 or col1 is null or col2 is null) and
  3              (col1 = col3 or col1 is null or col3 is null) and
  4              (col2 = col3 or col2 is null or col3 is null)
  5         then 'OK'
  6         else 'ERROR'
  7    end alignment
  8  from test;

COL1  COL2  COL3  ALIGNMENT
----- ----- ----- ----------
ABC   ABC   ABC   OK
ABC   ABC   NULL  OK
ABC   NULL  NULL  OK
NULL  ABC   ABC   OK
NULL  ABC   NULL  OK
NULL  NULL  NULL  OK
ABC   XYZ   ABC   ERROR
XYZ   XYZ   ABC   ERROR
ABC   NULL  XYZ   ERROR
XYZ   ABC   ABC   ERROR
NULL  NULL  XYZ   OK
XYZ   XYZ   NULL  OK
NULL  NULL  XYZ   OK

13 rows selected.

SQL>

CodePudding user response：

You can try with a single CASE expression, that assigns:

'OK', when Col3 = Col2 and Col2 = Col1
'ERROR', otherwise

You can deal with NULL values using the COALESCE function, but you'd need to use three conditions instead of only 2, as follows.

SELECT tab.*, 
       CASE WHEN COALESCE(COL_1, COL_2, 'NULL') = COALESCE(COL_2, COL_1, 'NULL')
             AND COALESCE(COL_2, COL_3, 'NULL') = COALESCE(COL_3, COL_2, 'NULL')
             AND COALESCE(COL_1, COL_3, 'NULL') = COALESCE(COL_3, COL_1, 'NULL')
            THEN 'OK'
            ELSE 'ERROR'
       END AS Alignment
FROM tab

Check the demo here.

Note: If you have 4 fields instead of 3, it's necessary to add conditions that check all paired fields, hence (1,3), (2,4), (3,4), (4,1).