Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: No Nulls? (was: Warehouse design: snowflake vs star schem

RE: No Nulls? (was: Warehouse design: snowflake vs star schem

From: Jesse, Rich <>
Date: Mon, 14 Oct 2002 10:48:34 -0800
Message-ID: <>

Hey Dick,

Thanks for your response. The reason I ask the question is because I *wish* our ERP system supported NULLs, at least in date fields. To properly explain why, I need to preface it with a short explanation:

Our 3rd party ERP system is one that was designed in the '80s using indexed files on VMS and possibly HP/MPE. It is written in a 4GL from Cognos called Powerhouse. When our vendor ported the ERP app to relational, I'm sure the non-normalized 4GL dictionary was maintained. And since the concept of NULL doesn't exist in a "flat" file (I hate that term -- RMS indexed files are *much* more than that!), NULLs are still not supported in the Oracle version of our ERP. This is fine except in the case of date fields.

When there is no data for a date column, our ERP vendor exploits a hole in the OCI that PowerHouse allows where the digit "0" is placed in a DATE field. No, not a date of "00/00/0000", but an undefined date that gets translated to roughly 12/30/1899. I believe that there is another date that can result, but I can't think of it of the top of my expanding forehead.

So, since NULLs aren't allowed in the date fields, we constantly need to check for these special date values when querying. Not being a student of normalization, I imagine that this would normally (small pun intended) be accomplished by moving the offending date field to another table?

Just trying to learn for the next time I get to work on a different ERP... :)


Rich Jesse                           System/Database Administrator              Quad/Tech International, Sussex, WI USA

> -----Original Message-----
> From: []
> Sent: Monday, October 14, 2002 12:25 PM
> To: Jesse, Rich; Multiple recipients of list ORACLE-L
> Subject: Re:No Nulls? (was: Warehouse design: snowflake vs star schem
> Jesse,
> I'll refrain from personal comments, but on CJ's quote,
> he's correct. Nulls
> are an oddity. They cannot be true or false (<column_name> = NULL or
> <column_name> != NULL), nor can they equal anything. They
> are in effect a third
> logical state of nothingness. You also have to code most
> applications with
> indicator variables to check for their existence. All in all
> a real pain in the
> backside. BUT, if you give me the possibility that nulls
> exist in the data I
> much prefer using them vs. many a third party solution of a
> single space. No
> application that I can reasonably think of should use NULLS,
> except those pre-81
> where there are obsolete columns.
> Dick Goulet

Please see the official ORACLE-L FAQ:
Author: Jesse, Rich

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Mon Oct 14 2002 - 13:48:34 CDT

Original text of this message