On 16/10/2008, at 8:00 AM, DeLong, Eric wrote:
Interestingly enough, nobody who complains about date default
'0001-01-01' ever complains about setting a numeric date field to
zero..... Why is the numeric date = 0 ok?
It's more that zero is not a valid date but 01/01/01 IS a valid date
so how to distinguish between a real valid date and a fake valid
date. You can't except by treating the fake valid date (i.e.,
01/01/01) as a special case. Special cases require tests in code.
That's no different from testing the null indicator.
Seems to me that this is really another case of "that's how we've
always done it". On S/3x we did not have NULL support therefore to
solve the problem of "no value" we used special values: zero, or
blank, or 999999, etc. Now that we DO have NULL support we have a way
of indicating this field REALLY HAS NO VALUE. We should be using the
new way instead of propagating the old way because, in this case, the
new way has technical benefits.
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. It's no
different from someone saying "Free-form RPG sucks and I'll write in
fixed-form". That's opinion. Technically it is wrong because I can
show many reasons why free-form is better and I can refute most
reasons for staying with fixed-form (possible technical exceptions
include: if your code must target previous releases, or needs some
obscure behaviour from a MOVE operation or similar). The correct
technical choice is to use free-form.
Using %NULLIND to set or test the NULL status of a field is no more
difficult than testing for a field being other than zero. If your
programmer's cannot grasp that then slap them until they do--or sack
them for failing to improve.
The only difficulty to using null-capable fields is deciding what to
display when printing or showing on a screen. DDS provides MAPFLD but
I think that is no help because it relies on mapping a valid value
(e.g., 01/01/01) to something else. Now you're back to treating valid
values as special cases which pretty much undoes the whole point of
NULL. SQL shows a dash which works for numeric or date/time/timestamp
NUll fields but not helpful for character fields. How do I know the
dash isn't really a dash?
Regardless of the display issues and the perceived coding
difficulties the correct choice for a field with NO VALUE is NULL.
I think part of the problem apparent in this discussion is confusion
over what default values really mean. Blank or zero doesn't mean NO
VALUE nor do they mean "I don't know IF there is a value or not".
They mean a zero value or blank value. NULL or blank for a
description field can both mean "no description" but really one is "a
blank description" and the other is NO VALUE. A numeric balance field
could have NULL or zero which could both mean "no balance" but really
one indicates a zero balance (implying either never had a balance or
did but now cleared) and the other is NO VALUE.
Thus we can see that using NULL for ALL fields in a row is probably
silly. Most columns can probably use the default value but in many
cases NULL makes sense. We're back to a TECHNICAL choice again.
Regards,
Simon Coulter.
--------------------------------------------------------------------
FlyByNight Software OS/400, i5/OS Technical Specialists
http://www.flybynight.com.au/
Phone: +61 2 6657 8251 Mobile: +61 0411 091 400 /"\
Fax: +61 2 6657 8251 \ /
X
ASCII Ribbon campaign against HTML E-Mail / \
--------------------------------------------------------------------
midrange.com
