Are the fields nulls?

[Guest]

Hi,
lets say I have 20 columns in a table.
What's the best way to check whether lets say 15 fields in a row are nulls?

thanx

alek_mil

 

[Brendan Reynolds]

15 specific fields, or any arbitrary 15 out of the 20?

 

[Steve Schapel]

Alek,

The desire to do an operation such as this, is an indicator that there
is probably a problem with your table design. In a properly structured
database, such a requirement would be very exceptional. If you are
interested in exploring this aspect, please post back with details or
examples of what these 15 fields relate to, and someone will be able to
advise.

Otherwise, make a query based on the table, and in the Field row of a
blank column in the query design grid, put the equivalent of this...
IsNull([Field1])+IsNull([Field2])+IsNull([Field3])+...+IsNull([Field15])
.... and in the Criteria of this column, put...
-15

 

[Guest]

15 specific fields

 

[Brendan Reynolds]

In that case, the most efficient solution is probably ...

WHERE Field1 IS NULL AND Field2 IS NULL ... AND Field15 IS NULL

.... where 'Field1', 'Field2', etc. are the names of the specific fields you
want to check.

Because 'Nulls propagate', meaning that if any part of an expression
evaluates to Null, the result of the entire expression will be Null, you can
shorten this to something like ...

WHERE (Field1 + Field2 ... + Field15) IS NULL

.... which is slightly shorter, but perhaps less clear in its intent.

 

[Steve Schapel]

... Because 'Nulls propagate', meaning that if any part of an expression
evaluates to Null, the result of the entire expression will be Null, you can
shorten this to something like ...

WHERE (Field1 + Field2 ... + Field15) IS NULL

... which is slightly shorter, but perhaps less clear in its intent.

Brendan, won't this tell you whether *any* of them is null, rather than
*all*?

 

[Guest]

One way

Where Len([Field1] & [Field2] & [Field3] ... & [Field15]) > 0

That will take care of cases where the field will be also empty and not only
Null.

 

[Brendan Reynolds]

Doh!

Yes, you're right, Steve.

The first example is probably more efficient anyway, as it would be able to
make use of any indexes that existed on those fields.

BTW: If the answer to my request for clarification had been 'arbitrary'
instead of 'specific', I'd have suggested a solution using a custom VBA
function, but I like your all-SQL solution better.

Excuse me for a minute now, I have to go drink some more coffee ...

 

[Steve Schapel]

Excuse me for a minute now, I have to go drink some more coffee ...

Hehe! At the time, the thought actually flitted through my head as to
what time in the morning it was at your place

Well, now it's 6:45am at my place, and no coffee yet, so hopefully the
brain is engaged!

Anyway, in practice, your first idea is the simplest, I think. So,
Alek, if you're still watching... From the point of view of the query
design, Brendan's suggestion simply involves writing:
Is Null
.... into the Criteria of each of the 15 fields.

 

[Jamie Collins]

The desire to do an operation such as this, is an indicator that there
is probably a problem with your table design. In a properly structured
database, such a requirement would be very exceptional. If you are
interested in exploring this aspect, please post back with details or
examples

I am interested in exploring this aspect.

Let's simplify the scenario a little. Say my table has a key_column and
five nullable columns named Field1 to Field5. Although these columns
are nullable, I have a business rule so that for each key_column value
I need *exactly* three (any three) to be non-null at all times. The
current structure makes the validation rule easy to write e.g.

-3 =
IsNull([Field1])+IsNull([Field2])+IsNull([Field3])+IsNull([Field4])+IsNull([Field5])

How could this table be 'properly' structured?

Thanks,
Jamie.

--