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 [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > 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: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
