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

Jesse,

    You would not happen to be talking about ManMan-X now would you??? Oh, the wonderful days of ManMan on HP TurboImage where the maximum date that it would populate was 32767 and date 1 was actually 31-OCT-1971. Yes, ManMan stored dates as a number where the starting point was 31-OCT-1971 and the latest date was 16-JUL-2061. What I imagine their doing is something similar. Actually your real problem was succinctly stated as "Our 3rd party ERP system ...".

Dick Goulet

____________________Reply Separator____________________
Author: "Jesse; Rich" <Rich.Jesse_at_qtiworld.com>
Date:       10/14/2002 12:47 PM

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... :)

Thanks!

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

> -----Original Message-----
> From: dgoulet_at_vicr.com [mailto:dgoulet_at_vicr.com]
> 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: http://www.orafaq.com
-- 
Author: 
  INET: dgoulet_at_vicr.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).