Numeric Null values in Quiz -

[Edis, Bob]

Dear Leslie

This is a database metadata design issue.  RDBMS's were not originally
designed to contain NULLs and they have been a pain in the but to DBAs, data
architects and programmers ever since they were added.

Is this a new decision to allow NULLs in your database tables?  If not how
have you handled them in the past with PH?  If so then you should probably
revisit the decision.  Rather than using NULL as the default 'value' you
could use a digit other than 0 or 1.  Your PH logic can then test for this
value.

If you choose to continue using NULLs in your data your PH
conditional/select logic in ALL of your programs must be retested and
include the 'IS NULL' or 'IS NOT NULL' syntax.  This is after the PHD has
been changed to allow NULL values. (NULL is not a value so this is an
oxymoron). 

Regards,
Blue

-----Original Message-----
From: Leslie Tsukamoto
To: powerh-l@lists.swau.edu
Sent: 12/17/2002 10:47 PM
Subject: Numeric Null values in Quiz - 

We are running an older version of Quiz (7.33.d3) on an Hp9000 and
accessing an Oracle 7.3.4 database.  Recently we added a few new columns
onto an existing table.  These columns are indicators that allow us to
track if a customer is a us-citizen or not.  When the new columns were
added, each existing record then had a null value in that column - it
was decided to test in our application that if the column was null when
the the record was being updated - we would force the user to update
that field as well and to set it to either (no = 0) or (yes = 1).  This
has worked fine from a front end application perspective - however, in
trying to report within quiz a listing of all customers and a value of
Yes, No, or ? for the 'us-indicator' field, we found that pretty much
everyone was showing up on our report as No - upon further investigation
- it appears that Quiz is (on it's own) apparently determining if we
have a numeric field and the value is null - quiz assumes it to be 0 -
we are unable to successfully test against a null value on that field
even though we can with sqlplus and other applications.  
 
Can someone tell me - is there something that we can do to have quiz
recognize these column values as Nulls so that we can determine which
records have been updated to a true NO value and which ones haven't been
updated at all ?  We have been forced to code this particular report in
sqlplus which deviates from the normal deployment of reporting.
 
I am not sure if there is a dictionary setting that we can utilize or
not - any ideas?

Sincerely,  Leslie 

Leslie J. Tsukamoto 
Manager of Programming, CAE SimuFlite 
ph:  407-445-0226 
fax: 407-295-9493 
email: leslie.tsukamoto@cae.com