RE: Interesting question and debate on ddl tables with datefieldsthatwill not always have a value

They most likely will not understand it.
I don't see that as a problem however. History of SQL is that its original
design was supposed to be easy enough for managers to use (Structured
English Query Language). That did not happen. It turned out however that
it had enough 'Structure' in it for most techies to easily understand it.
So techies built reporting tools on top of SQL (on all db platforms). Some
of these reporting tools expose the option to type SQL statement directly
and end-users try it, with varied success.

Fact is that all SQL built-ins understand NULLs very well and actually in
some cases protect the end-users from making unintended mistakes. For
example, let's say you have a numeric field and have a choice of a default
value or a NULL. If you go with a default value, the AVG aggregate function
will include the default value just as any other value. That will likely
produce incorrect results. Same goes for MIN, MAX, SUM, COUNT. Of course,
YOU (the techie) can handle these special cases with a simple WHERE clause,
but end-user won't know that (at least not until he sees the default values
included in the output or in my example some outrageous AVG returned).
If you went with a NULL, you can be assured that all SQL built-ins
understand it and treat it in a well documented, standard manner. In case
of the aggregate functions like AVG, you know it wouldn't include a NULL in
the final result, since it's not a value (NULL means 'void of value'). If
you compare two NULL fields in fact, you'll get FALSE as a result. I know
it's not intuitive, but if you think about it, how can you compare two
fields whose values were never entered in the first place.

But as I said (much) earlier in this thread, fact is that most RLA
developers never had to work with NULLs and don't have an 'easy' way to
handle NULLs. Since on System i these developers will work with the
database using RLA, it makes sense to make their jobs easier and less error
prone. In most shops it's the RLA developers writing business critical
applications and are deemed more important than any end-user queries or
reporting tools (deservedly so).

I have also worked with a number of very large System i accounts are using
SQL exclusively. For these customers, NULL handling is not a big issue and
they have more latitude in choosing to use it or not.

As Joe said, it's really a business decision, and presented with all the
facts I guarantee you that different businesses will make a different call.
Doug will just have to make his on his own. Good luck Doug :)

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: RE: Interesting question and debate on ddl tables with
datefieldsthatwill not always have a value

So far this discussion has been focused on IT professionals and the proper
user of null fields. What happens when you bring a non-IT user into this
discussion and they are using some package to query that same database. Are
these employees going to understand the concept of a null field and how to
get the correct results from their query when a field is null capable? Will
they understand that field = blank is not the same as field = null?