Reduce the use of the AND operator in Oracle when comparing multiple NULL values ​​in different columns

Question

I want to compare multiple values ​​of a NULL column. eg. Suppose I have 3 columns in my table from which I should learn NOT NULL values. I am using the following code:

select * from table1 where column1 is not null and column2 is not null and column3 is not null 

I do not want to use this code because it uses "and" several times if the columns continue to grow. Does anyone have an option for this in Oracle 11g?

Answer 1

I agree with the comment that your request is in order. If the columns you are checking are numeric, then you can take advantage of Oracle's zero-valued behavior to shorten the query as follows:

 select * from table 1 where ( column1 + column2 + column3 ) is not null; 

If any of the listed columns is zero, then the sum will also be zero. Unfortunately, if you have strings, zero strings are concatenated just fine, so the same approach does not work with them.

Answer 2

you can use

 COALESCE (expr1, expr2) 

which is equivalent

 CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END 

Your syntax will be

 coalesce(column1,....,columnn) is not null 

Answer 3

You can use this instead of COALESCE:

 SELECT * FROM table1 WHERE column1 || column2 || column3 || column4 IS NOT NULL; 

Answer 4

Tim Raine answers well. If you have all the row columns, the where clause might be:

  WHERE LENGTH(COLUMN1)+LENGTH(COLUMN2)+LENGTH(COLUMN3) IS NOT NULL 

If you had a combination of string and numeric:

  WHERE COLUMN_INTEGER1+COLUMN_INTEGER2+LENGTH(COLUMN_STRING3) IS NOT NULL