Re: forcing null values into #variables?

[Charles Thompson <THOMPSONC@TRANSPOWER.CO.NZ>]

Steve,

Sorry to stick my nose in here, but I feel I must have my say !

You seem to have run afoul (note nautical reference) of a database design trap:
 Trying to derive multiple meanings from a single field.  Numeric fields should
NEVER allow nulls, in my view (and SQR agrees).  You should use another field
if you wish to convey one or more of the following :
- The numeric value has not yet been entered
- The value is not known
- The value has been cleared/deleted
- The field is invalid based on other field values

In this case, another Yes/No field should be used to indicate that there is no
information available currently.

- Charles Thompson

>>>Why is a zero unacceptable?  The business I'm supporting is Shipping Loss
>>>Control.  The column in question is a measurement of liquid cargo lost.  The
>>>measurement may not have been taken, in which case a NULL is the correct
>>>answer (no information available).  If the measurement was taken and zero is
>>>the answer, we had a very good voyage (no cargo lost).  I can't use zeroes,
>>>or any other number, to represent the lack of a measurement.  Sybase
>>>understands the distinction and allows resetting numeric columns to NULL in
>>>ISQL.  Why shouldn't it be possible in SQR?  I'm trying to retain the
>>>numeric attributes (so I can do more math with the number) for when I do
>>>have a measurement.
>>>
>>>Thanks,
>>>Steve
>>>> -----Original Message-----
>>>> From: Wanko, Christopher G, CFCTR [SMTP:apollo@att.com]
>>>> Sent: Monday, December 14, 1998 11:44 AM
>>>> To:   'SMAU@CHEVRON.COM'
>>>> Subject:      RE: forcing null values into #variables?
>>>>
>>>> > Is there a way to force a null value into a #variable?  I
>>>>
>>>        (SNIP)
>>>
>>>> You may different responses, but perhaps you might explain why a zero is
>>>> an
>>>> unacceptable result to you.
 >>>>
>>>        (SNIP)
>>>
>>>> -Chris
>>>>
>>>>