Understanding and use of a NULL concept is required when using SQL.
If I create a LEFT OUTER JOIN sql query, project some columns from the
secondary dial and some of the join conditions have no match in the
secondary dial, the result set will include NULLs even though the tables
being joined have no NULL capable fields. If this query is in embedded SQL,
the developer has to handle this case.
Traditional RLA developer may handle it by forcing the secondary dial
projections to a non-NULL field using COALESCE, but that is not the best way
to handle it. Using null indicators is.
Again, if no SQL access is used, value of understanding and use of NULL
diminishes, although it doesn't disappear.
What I think has not been mentioned yet in this thread is that most of the
other database platforms use SQL exclusively to access the database, so
developing understanding of these types of concepts could benefit developers
whose retirement is not just around the corner by making them more versatile
and marketable. Or simply able to help their non-i development team.
Elvis
Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: Thursday, October 16, 2008 11:44 PM
To: Midrange Systems Technical Discussion
Subject: Re: Interesting question and debate on ddl tables withdate
fieldsthat will not always have a value
Simon Coulter wrote:
This is not an opinion choice nor a business decision. It's a
technical one. If you have a column that can contain NO VALUE then
the correct technical choice is to use NULLs. That's it.
This is the other example of what this list has become. The above
statement is hooey - that's the correct technical term.
Remember, we're not even discussing the null value. No, the focus is on
RPG's particular syntax of the null indicator, which when it boils down
to it is no more than a flag field; just one that can be brought along
from the database by using a few syntactical gyrations.
Think about it - a flag that basically indicates that a single specific
value is in a field. How is that better than simply checking for a
specific value?
The argument has more merit when you abstract it from the RPG
implementation and discuss it purely in the database sense. At that
point, the NULL value does allow some rather arcane behavior in
set-based processing, and if you find yourself in need of summing,
counting and aggregating records in ways that require records to be
eliminated because they have missing values, then you have a good
business case for nulls. Note that this is different than Simon's
assertion above that says simply that if you can have no value, you must
use null. This says that if your business case requires you to omit
records from set-based processing if they are missing values, then null
will make it easier.
Remember, there's nothing magic about null. It's nothing more than a
hidden flag in the database, one that happens to be built into the
ubiquitous SQL syntax. It is not required for a relational database and
indeed some argue against it entirely, while others assert that, like
infinity, there are different orders of null.
It's a tool, and the use of any tool is... a business decision.
Okay, that's 15 minutes wasted. I should be sleeping...
Joe
midrange.com
