Oracle Sql Syntax - Check Multiple Columns For Is Not Null

Is there a more concise syntax for checking whether multiple columns are not null? I'm looking to simplify weight IS NOT NULL AND weight_unit IS NOT NULL AND length IS NOT NULL AND

Solution 1:

With De Morgan's law:

NOT (A OR B) = (NOT A) AND (NOT B)

you save 20 chars ;)

NOT (
weight IS NULL OR
weight_unit IS NULL OR
length IS NULL OR
width IS NULL OR
height IS NULL OR
dimensional_unit IS NULL)

Solution 2:

As far as I know, there isn't such a syntax.

But if all of them are numeric you can use this trick:

weight + weight_unit + length + width + height + dimensional_unit isnotnull

Solution 3:

ALTERTABLE X
   ADDCONSTRAINT C_X_NN
      CHECK (  (
               DECODE (weight, NULL, 0, 1) +
               DECODE (weight_unit, NULL, 0, 1) +
               DECODE (length      , NULL, 0, 1) +
               DECODE (width     , NULL, 0, 1) +
               DECODE (height      , NULL, 0, 1) +
               DECODE (dimensional_unit , NULL, 0, 1)
               ) =0
            );

Solution 4:

If you want to check if ALL the columns are NOT NULL then you could Concatenate your columns first and then test once for IS NOT NULL e.g:

weight || weight_unit ||length|| width || height || dimensional_unit IS NOT NULL

This will check if all the values together across those fields do not come to a total of NULL.

If just testing whether there may be a NULL among them then I think your original statement from the question is still the best way to do it.

Solution 5:

I tried to solve this problem, If all columns/variables are of type numeric or varchar2 then we can use following way in sql, pl/sql :

LENGTH(COL1||COL2||COL3) IS NOT NULL 

Hope this will help you.