Grokbase › Groups › Oracle › oracle-l › October 2002
FAQ
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 ( = NULL or
!= 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

____________________Reply Separator____________________
Author: "Jesse; Rich"
Date: 10/14/2002 9:33 AM

On the link below is this quote from C.J.Date:

"I don't want you to think that my SQL solution to your problem means I
advocate the use of nulls. Nulls are a disaster."

Of course, he doesn't expound upon it (probably not a need except for
dummies like me). Anyone care to comment? (On the quote, not on my
dumminess...)

Rich

Rich Jesse System/Database Administrator
Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
-----Original Message-----
From: Robson, Peter
Sent: Monday, October 14, 2002 4:59 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Warehouse design: snowflake vs star schemas


Just for the record (and perhaps to confirm that there are
always two sides
to a story). Readers may like to see the article Chris Date
wrote to Ralph
Kemball on the subject of business rules and integrity constraints:

http://www.dbdebunk.com/kimball1.htm
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.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).
--
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).

Search Discussions

  • Jesse, Rich at Oct 14, 2002 at 6:48 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
    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 ( = NULL or
    != 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: Rich.Jesse_at_qtiworld.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).
  • Adams, Matthew (GECP, MABG, 088130) at Oct 14, 2002 at 7:39 pm ⇧
    "No application that I can reasonably think of should
    use NULLS, except those pre-81
    where there are obsolete columns."

    Everytime somebody says this to me, I ask them:

    How do you handle still employed employees in an EMPLOYEE table
    that contains a END_EMPLOYEMENT date column?

    What's your take?

    Matt Adams - GE Appliances - matt.adams_at_appl.ge.com
    Write a poem about a haircut! But lofty, noble, tragic, full of love,
    treachery, retribution, quiet heroism in the face of certain doom!
    Six lines, cleverly rhymed, and every word beginning with the letter s!

    -----Original Message-----
    Sent: Monday, October 14, 2002 2:29 PM
    To: Multiple recipients of list ORACLE-L

    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 ( = NULL or
    != 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

    ____________________Reply Separator____________________
    Author: "Jesse; Rich"
    Date: 10/14/2002 9:33 AM

    On the link below is this quote from C.J.Date:

    "I don't want you to think that my SQL solution to your problem means I
    advocate the use of nulls. Nulls are a disaster."

    Of course, he doesn't expound upon it (probably not a need except for
    dummies like me). Anyone care to comment? (On the quote, not on my
    dumminess...)

    Rich

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
    -----Original Message-----
    From: Robson, Peter
    Sent: Monday, October 14, 2002 4:59 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Warehouse design: snowflake vs star schemas


    Just for the record (and perhaps to confirm that there are
    always two sides
    to a story). Readers may like to see the article Chris Date
    wrote to Ralph
    Kemball on the subject of business rules and integrity constraints:

    http://www.dbdebunk.com/kimball1.htm
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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).
    --
    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).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Adams, Matthew (GECP, MABG, 088130)
    INET: MATT.ADAMS_at_APPL.GE.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).
  • DENNIS WILLIAMS at Oct 14, 2002 at 7:39 pm ⇧
    Rich

    If you were to give each of your developers a pop quiz on nulls, how many
    do you think would pass? I agree with Dick, that nulls are a confusing
    concept that usually only the DBA really understands. I feel that an
    important decision when beginning a new application is whether or not to use
    nulls. My feeling is either to make a policy of not using nulls or use them
    wherever appropriate. I hate the situation where there is only one table in
    the entire application that uses nulls.

    We have the same situation with our ERP package. "no date" is stored as
    01/01/1700. Just last week I had a question from a developer that was trying
    to directly access the data for this application in Oracle about that.

    Dennis Williams
    DBA

    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Monday, October 14, 2002 1:49 PM
    To: Multiple recipients of list ORACLE-L

    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
    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 ( = NULL or
    != 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: Rich.Jesse_at_qtiworld.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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.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).
  • Igor Neyman at Oct 14, 2002 at 8:14 pm ⇧
    RE: No Nulls? (was: Warehouse design: snowflake vs star schemEND_EMPLOYEMENT date for still employed employees equals to "01/01/4000" (or any other pre-defined date in distant future).

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com


    Original Message -----
    From: Adams, Matthew (GECP, MABG, 088130)
    To: Multiple recipients of list ORACLE-L
    Sent: Monday, October 14, 2002 3:39 PM
    Subject: RE: No Nulls? (was: Warehouse design: snowflake vs star schem

    "No application that I can reasonably think of should
    use NULLS, except those pre-81
    where there are obsolete columns."

    Everytime somebody says this to me, I ask them:

    How do you handle still employed employees in an EMPLOYEE table
    that contains a END_EMPLOYEMENT date column?

    What's your take?

    Matt Adams - GE Appliances - matt.adams_at_appl.ge.com
    Write a poem about a haircut! But lofty, noble, tragic, full of love,
    treachery, retribution, quiet heroism in the face of certain doom!
    Six lines, cleverly rhymed, and every word beginning with the letter s!

    -----Original Message-----
    From: dgoulet_at_vicr.com
    Sent: Monday, October 14, 2002 2:29 PM
    To: 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 ( = NULL or
    != 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

    ____________________Reply Separator____________________
    Author: "Jesse; Rich"
    Date: 10/14/2002 9:33 AM

    On the link below is this quote from C.J.Date:

    "I don't want you to think that my SQL solution to your problem means I
    advocate the use of nulls. Nulls are a disaster."

    Of course, he doesn't expound upon it (probably not a need except for
    dummies like me). Anyone care to comment? (On the quote, not on my
    dumminess...)

    Rich

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
    -----Original Message-----
    From: Robson, Peter
    Sent: Monday, October 14, 2002 4:59 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Warehouse design: snowflake vs star schemas
    >
    >
    Just for the record (and perhaps to confirm that there are
    always two sides
    to a story). Readers may like to see the article Chris Date
    wrote to Ralph
    Kemball on the subject of business rules and integrity constraints: >
    http://www.dbdebunk.com/kimball1.htm
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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).
    --
    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).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Igor Neyman
    INET: ineyman_at_perceptron.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).
  • Fink, Dan at Oct 14, 2002 at 8:49 pm ⇧
    The problem I see with NO NULLS is that artificial data must be created,
    where the data is truly not known. Whether you deal with NULLs or artificial
    data, you will always have to code accordingly, so it is a wash. Igor's
    example is an good one. When I write an app to access the END_EMPLOYMENT
    date, I must handle a date of '01/01/4000'. Or I can handle the NULL
    condition. As a person who has had to support some very convoluted code, I'd
    rather deal with NULL. What if the employee record contained TERM_CODE? I
    would rather have the value NULL, meaning they have not been terminated
    rather than dealing with hard-coded or lookup values.

    -----Original Message-----
    Sent: Monday, October 14, 2002 2:14 PM
    To: Multiple recipients of list ORACLE-L

    END_EMPLOYEMENT date for still employed employees equals to "01/01/4000" (or
    any other pre-defined date in distant future).


    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com




    Original Message -----
    To: Multiple recipients of list ORACLE-L
    Sent: Monday, October 14, 2002 3:39 PM

    "No application that I can reasonably think of should
    use NULLS, except those pre-81
    where there are obsolete columns."

    Everytime somebody says this to me, I ask them:

    How do you handle still employed employees in an EMPLOYEE table
    that contains a END_EMPLOYEMENT date column?

    What's your take?

    Matt Adams - GE Appliances - matt.adams_at_appl.ge.com
    Write a poem about a haircut! But lofty, noble, tragic, full of love,
    treachery, retribution, quiet heroism in the face of certain doom!
    Six lines, cleverly rhymed, and every word beginning with the letter s!

    -----Original Message-----

    Sent: Monday, October 14, 2002 2:29 PM
    To: Multiple recipients of list ORACLE-L

    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 ( = NULL or
    != 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

    ____________________Reply Separator____________________
    Author: "Jesse; Rich"
    Date: 10/14/2002 9:33 AM

    On the link below is this quote from C.J.Date:

    "I don't want you to think that my SQL solution to your problem means I
    advocate the use of nulls. Nulls are a disaster."

    Of course, he doesn't expound upon it (probably not a need except for
    dummies like me). Anyone care to comment? (On the quote, not on my
    dumminess...)

    Rich

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
    -----Original Message-----
    From: Robson, Peter [ mailto:pgro_at_bgs.ac.uk ]
    Sent: Monday, October 14, 2002 4:59 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Warehouse design: snowflake vs star schemas


    Just for the record (and perhaps to confirm that there are
    always two sides
    to a story). Readers may like to see the article Chris Date
    wrote to Ralph
    Kemball on the subject of business rules and integrity constraints:

    http://www.dbdebunk.com/kimball1.htm
    <http://www.dbdebunk.com/kimball1.htm>

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    <http://www.orafaq.com>
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    <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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    <http://www.orafaq.com>
    --
    Author:
    INET: dgoulet_at_vicr.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    <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).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Fink, Dan
    INET: Dan.Fink_at_mdx.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).
  • Marc Perkowitz at Oct 14, 2002 at 9:04 pm ⇧
    RE: No Nulls? (was: Warehouse design: snowflake vs star schemI disagree with the use of "dummy" values to represent missing data. It reminds me of the olden days when we coded in 12/31/99 and such. Shades of COBOL HIGH-VALUES! You're introducing a lot of dependencies and non-intuitive information. For instance, how do you ensure that these dummy values are used consistently across an application or database?

    Yes, you will simply your SQL and perhaps improve performance (there are workarounds), but you will corresponding need to add special logic to reports and screens to suppress displaying these dummy values. Or you'll have to explain to the HR manager why all of the employees appear to be terminated!

    A null value means there is missing data. It is not clear that 01/01/4000 represents missing data or incorrectly entered data.

    Leave me my nulls!

    Marc Perkowitz

    Original Message -----
    From: Igor Neyman
    To: Multiple recipients of list ORACLE-L
    Sent: Monday, October 14, 2002 3:14 PM
    Subject: Re: No Nulls? (was: Warehouse design: snowflake vs star schem

    END_EMPLOYEMENT date for still employed employees equals to "01/01/4000" (or any other pre-defined date in distant future).

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com


    Original Message -----
    From: Adams, Matthew (GECP, MABG, 088130)
    To: Multiple recipients of list ORACLE-L
    Sent: Monday, October 14, 2002 3:39 PM
    Subject: RE: No Nulls? (was: Warehouse design: snowflake vs star schem

    "No application that I can reasonably think of should
    use NULLS, except those pre-81
    where there are obsolete columns."

    Everytime somebody says this to me, I ask them:

    How do you handle still employed employees in an EMPLOYEE table
    that contains a END_EMPLOYEMENT date column?

    What's your take?

    Matt Adams - GE Appliances - matt.adams_at_appl.ge.com
    Write a poem about a haircut! But lofty, noble, tragic, full of love,
    treachery, retribution, quiet heroism in the face of certain doom!
    Six lines, cleverly rhymed, and every word beginning with the letter s!

    -----Original Message-----
    From: dgoulet_at_vicr.com
    Sent: Monday, October 14, 2002 2:29 PM
    To: 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 ( = NULL or
    != 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

    ____________________Reply Separator____________________
    Author: "Jesse; Rich"
    Date: 10/14/2002 9:33 AM

    On the link below is this quote from C.J.Date:

    "I don't want you to think that my SQL solution to your problem means I
    advocate the use of nulls. Nulls are a disaster."

    Of course, he doesn't expound upon it (probably not a need except for
    dummies like me). Anyone care to comment? (On the quote, not on my
    dumminess...)

    Rich

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
    -----Original Message-----
    From: Robson, Peter
    Sent: Monday, October 14, 2002 4:59 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Warehouse design: snowflake vs star schemas
    >
    >
    Just for the record (and perhaps to confirm that there are
    always two sides
    to a story). Readers may like to see the article Chris Date
    wrote to Ralph
    Kemball on the subject of business rules and integrity constraints: >
    http://www.dbdebunk.com/kimball1.htm
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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).
    --
    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).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Marc Perkowitz
    INET: mperkowitz_at_twjconsulting.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).
  • Grabowy, Chris at Oct 14, 2002 at 9:10 pm ⇧
    Hmmm...but what about the index? Which is faster?


    select * from table where END_EMPLOYMENT IS NULL;


    OR


    select * from table where END_EMPLOYMENT = '01/01/4000';


    I like NULL, but I am leaning towards Igor, and others, to agree upon
    and use a default value, or a "business sense" replacement value for
    NULL. I want to be able to take the awesome advantage of an
    index...versus FTS?


    Am I headed in the wrong direction?? Any other thoughts??

    -----Original Message-----
    From: Fink, Dan
    Sent: Monday, October 14, 2002 4:49 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: No Nulls? (was: Warehouse design: snowflake vs star

    schem



    The problem I see with NO NULLS is that artificial data must be
    created, where the data is truly not known. Whether you deal with NULLs
    or artificial data, you will always have to code accordingly, so it is a
    wash. Igor's example is an good one. When I write an app to access the
    END_EMPLOYMENT date, I must handle a date of '01/01/4000'. Or I can
    handle the NULL condition. As a person who has had to support some very
    convoluted code, I'd rather deal with NULL. What if the employee record
    contained TERM_CODE? I would rather have the value NULL, meaning they
    have not been terminated rather than dealing with hard-coded or lookup
    values.

    -----Original Message-----
    From: Igor Neyman
    Sent: Monday, October 14, 2002 2:14 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: No Nulls? (was: Warehouse design: snowflake

    vs star schem



    END_EMPLOYEMENT date for still employed employees equals
    to "01/01/4000" (or any other pre-defined date in distant future).


    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com

    ----- Original Message -----
    From: Adams, Matthew (GECP, MABG, 088130)

    To: Multiple recipients of list ORACLE-L

    Sent: Monday, October 14, 2002 3:39 PM
    Subject: RE: No Nulls? (was: Warehouse design:

    snowflake vs star schem

    "No application that I can reasonably think of
    should

    use NULLS, except those pre-81
    where there are obsolete columns."

    Everytime somebody says this to me, I ask them:

    How do you handle still employed employees in an
    EMPLOYEE table
    that contains a END_EMPLOYEMENT date column?

    What's your take?
    ----
    Matt Adams - GE Appliances -
    matt.adams_at_appl.ge.com
    Write a poem about a haircut! But lofty, noble,
    tragic, full of love,
    treachery, retribution, quiet heroism in the
    face of certain doom!
    Six lines, cleverly rhymed, and every word

    beginning with the letter s!

    -----Original Message-----
    From: dgoulet_at_vicr.com

    Sent: Monday, October 14, 2002 2:29 PM
    To: 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
    ( = NULL or

    != 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

    ____________________Reply
    Separator____________________
    Author: "Jesse; Rich"
    Date: 10/14/2002 9:33 AM

    On the link below is this quote from C.J.Date:

    "I don't want you to think that my SQL solution
    to your problem means I
    advocate the use of nulls. Nulls are a

    disaster."

    Of course, he doesn't expound upon it (probably
    not a need except for

    dummies like me). Anyone care to comment? (On
    the quote, not on my

    dumminess...)

    Rich

    Rich Jesse
    System/Database Administrator

    Rich.Jesse_at_qtiworld.com Quad/Tech
    International, Sussex, WI USA
    -----Original Message-----
    From: Robson, Peter
    Sent: Monday, October 14, 2002 4:59 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Warehouse design: snowflake vs
    star schemas
    >
    >
    Just for the record (and perhaps to confirm
    that there are
    always two sides
    to a story). Readers may like to see the
    article Chris Date
    wrote to Ralph
    Kemball on the subject of business rules and
    integrity constraints:
    >
    http://www.dbdebunk.com/kimball1.htm
    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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).

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

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Grabowy, Chris
    INET: cgrabowy_at_fcg.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).
  • Igor Neyman at Oct 14, 2002 at 9:10 pm ⇧
    RE: No Nulls? (was: Warehouse design: snowflake vs star schemActually, you don't have to deal with "01/01/4000" date (at least on "select"), all you have to do in order find currently employed employees, is:

    where END_EMPLOYMENT > sysdate

    as for inserts, all you have to do, is define "01/01/4000" as a default value for END_EMPLOYMENT,
    also, not allowing NULLs, makes it easier for indexing.

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com


    Original Message -----
    From: Fink, Dan
    To: Multiple recipients of list ORACLE-L
    Sent: Monday, October 14, 2002 4:49 PM
    Subject: RE: No Nulls? (was: Warehouse design: snowflake vs star schem

    The problem I see with NO NULLS is that artificial data must be created, where the data is truly not known. Whether you deal with NULLs or artificial data, you will always have to code accordingly, so it is a wash. Igor's example is an good one. When I write an app to access the END_EMPLOYMENT date, I must handle a date of '01/01/4000'. Or I can handle the NULL condition. As a person who has had to support some very convoluted code, I'd rather deal with NULL. What if the employee record contained TERM_CODE? I would rather have the value NULL, meaning they have not been terminated rather than dealing with hard-coded or lookup values.
    -----Original Message-----
    From: Igor Neyman
    Sent: Monday, October 14, 2002 2:14 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: No Nulls? (was: Warehouse design: snowflake vs star schem

    END_EMPLOYEMENT date for still employed employees equals to "01/01/4000" (or any other pre-defined date in distant future).

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com


    Original Message -----
    From: Adams, Matthew (GECP, MABG, 088130)
    To: Multiple recipients of list ORACLE-L
    Sent: Monday, October 14, 2002 3:39 PM
    Subject: RE: No Nulls? (was: Warehouse design: snowflake vs star schem

    "No application that I can reasonably think of should

    use NULLS, except those pre-81
    where there are obsolete columns."

    Everytime somebody says this to me, I ask them:

    How do you handle still employed employees in an EMPLOYEE table
    that contains a END_EMPLOYEMENT date column?

    What's your take?
    ----
    Matt Adams - GE Appliances - matt.adams_at_appl.ge.com
    Write a poem about a haircut! But lofty, noble, tragic, full of love,
    treachery, retribution, quiet heroism in the face of certain doom!
    Six lines, cleverly rhymed, and every word beginning with the letter s!

    -----Original Message-----
    From: dgoulet_at_vicr.com
    Sent: Monday, October 14, 2002 2:29 PM
    To: 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 ( = NULL or
    != 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

    ____________________Reply Separator____________________
    Author: "Jesse; Rich"
    Date: 10/14/2002 9:33 AM

    On the link below is this quote from C.J.Date:

    "I don't want you to think that my SQL solution to your problem means I
    advocate the use of nulls. Nulls are a disaster."

    Of course, he doesn't expound upon it (probably not a need except for
    dummies like me). Anyone care to comment? (On the quote, not on my
    dumminess...)

    Rich

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
    -----Original Message-----
    From: Robson, Peter
    Sent: Monday, October 14, 2002 4:59 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Warehouse design: snowflake vs star schemas
    >
    >
    Just for the record (and perhaps to confirm that there are
    always two sides
    to a story). Readers may like to see the article Chris Date
    wrote to Ralph
    Kemball on the subject of business rules and integrity constraints: >
    http://www.dbdebunk.com/kimball1.htm
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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).
    --
    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).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Igor Neyman
    INET: ineyman_at_perceptron.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).
  • Igor Neyman at Oct 14, 2002 at 9:18 pm ⇧
    RE: No Nulls? (was: Warehouse design: snowflake vs star schem>Or you'll have to explain to the HR manager why all of the employees appear to be terminated!

    they aren't terminated! at least not yet :-)

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com


    Original Message -----
    From: Marc Perkowitz
    To: Multiple recipients of list ORACLE-L
    Sent: Monday, October 14, 2002 5:04 PM
    Subject: Re: No Nulls? (was: Warehouse design: snowflake vs star schem

    I disagree with the use of "dummy" values to represent missing data. It reminds me of the olden days when we coded in 12/31/99 and such. Shades of COBOL HIGH-VALUES! You're introducing a lot of dependencies and non-intuitive information. For instance, how do you ensure that these dummy values are used consistently across an application or database?

    Yes, you will simply your SQL and perhaps improve performance (there are workarounds), but you will corresponding need to add special logic to reports and screens to suppress displaying these dummy values. Or you'll have to explain to the HR manager why all of the employees appear to be terminated!

    A null value means there is missing data. It is not clear that 01/01/4000 represents missing data or incorrectly entered data.

    Leave me my nulls!

    Marc Perkowitz

    Original Message -----
    From: Igor Neyman
    To: Multiple recipients of list ORACLE-L
    Sent: Monday, October 14, 2002 3:14 PM
    Subject: Re: No Nulls? (was: Warehouse design: snowflake vs star schem

    END_EMPLOYEMENT date for still employed employees equals to "01/01/4000" (or any other pre-defined date in distant future).

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com


    Original Message -----
    From: Adams, Matthew (GECP, MABG, 088130)
    To: Multiple recipients of list ORACLE-L
    Sent: Monday, October 14, 2002 3:39 PM
    Subject: RE: No Nulls? (was: Warehouse design: snowflake vs star schem

    "No application that I can reasonably think of should

    use NULLS, except those pre-81
    where there are obsolete columns."

    Everytime somebody says this to me, I ask them:

    How do you handle still employed employees in an EMPLOYEE table
    that contains a END_EMPLOYEMENT date column?

    What's your take?
    ----
    Matt Adams - GE Appliances - matt.adams_at_appl.ge.com
    Write a poem about a haircut! But lofty, noble, tragic, full of love,
    treachery, retribution, quiet heroism in the face of certain doom!
    Six lines, cleverly rhymed, and every word beginning with the letter s!

    -----Original Message-----
    From: dgoulet_at_vicr.com
    Sent: Monday, October 14, 2002 2:29 PM
    To: 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 ( = NULL or
    != 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

    ____________________Reply Separator____________________
    Author: "Jesse; Rich"
    Date: 10/14/2002 9:33 AM

    On the link below is this quote from C.J.Date:

    "I don't want you to think that my SQL solution to your problem means I
    advocate the use of nulls. Nulls are a disaster."

    Of course, he doesn't expound upon it (probably not a need except for
    dummies like me). Anyone care to comment? (On the quote, not on my
    dumminess...)

    Rich

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
    -----Original Message-----
    From: Robson, Peter
    Sent: Monday, October 14, 2002 4:59 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Warehouse design: snowflake vs star schemas
    >
    >
    Just for the record (and perhaps to confirm that there are
    always two sides
    to a story). Readers may like to see the article Chris Date
    wrote to Ralph
    Kemball on the subject of business rules and integrity constraints: >
    http://www.dbdebunk.com/kimball1.htm
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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).
    --
    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).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Igor Neyman
    INET: ineyman_at_perceptron.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).
  • Ora NT DBA at Oct 14, 2002 at 9:53 pm ⇧
    Both would likely do FTS since at any given time more than
    50% of your employees will be current (have an end date
    of 1/1/4000' making it very unlikely that the cbo would choose
    this index. The RBO, would, but it would likely degrade
    not improve your performance.

    John

    Grabowy, Chris wrote:
    Hmmm...but what about the index? Which is faster?

    select * from table where END_EMPLOYMENT IS NULL;

    OR

    select * from table where END_EMPLOYMENT = '01/01/4000';

    I like NULL, but I am leaning towards Igor, and others, to agree upon
    and use a default value, or a "business sense" replacement value for
    NULL. I want to be able to take the awesome advantage of
    an index...versus FTS?

    Am I headed in the wrong direction?? Any other thoughts??

    -----Original Message-----
    From: Fink, Dan
    Sent: Monday, October 14, 2002 4:49 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: No Nulls? (was: Warehouse design: snowflake vs star schem

    The problem I see with NO NULLS is that artificial data must be
    created, where the data is truly not known. Whether you deal with
    NULLs or artificial data, you will always have to code
    accordingly, so it is a wash. Igor's example is an good one. When
    I write an app to access the END_EMPLOYMENT date, I must handle a
    date of '01/01/4000'. Or I can handle the NULL condition. As a
    person who has had to support some very convoluted code, I'd
    rather deal with NULL. What if the employee record contained
    TERM_CODE? I would rather have the value NULL, meaning they have
    not been terminated rather than dealing with hard-coded or lookup
    values.

    -----Original Message-----
    From: Igor Neyman
    Sent: Monday, October 14, 2002 2:14 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: No Nulls? (was: Warehouse design: snowflake vs
    star schem

    END_EMPLOYEMENT date for still employed employees equals to
    "01/01/4000" (or any other pre-defined date in distant future).

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com




    ----- Original Message -----
    From: Adams, Matthew (GECP, MABG, 088130)

    To: Multiple recipients of list ORACLE-L

    Sent: Monday, October 14, 2002 3:39 PM
    Subject: RE: No Nulls? (was: Warehouse design: snowflake
    vs star schem

    "No application that I can reasonably think of should
    use NULLS, except those pre-81
    where there are obsolete columns."

    Everytime somebody says this to me, I ask them:

    How do you handle still employed employees in an EMPLOYEE
    table
    that contains a END_EMPLOYEMENT date column?

    What's your take?
    ----
    Matt Adams - GE Appliances - matt.adams_at_appl.ge.com
    Write a poem about a haircut! But lofty, noble, tragic,
    full of love,
    treachery, retribution, quiet heroism in the face of
    certain doom!
    Six lines, cleverly rhymed, and every word beginning with
    the letter s!

    -----Original Message-----
    From: dgoulet_at_vicr.com
    Sent: Monday, October 14, 2002 2:29 PM
    To: 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
    ( = NULL or
    != 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

    ____________________Reply Separator____________________
    Author: "Jesse; Rich"
    Date: 10/14/2002 9:33 AM

    On the link below is this quote from C.J.Date:

    "I don't want you to think that my SQL solution to your
    problem means I
    advocate the use of nulls. Nulls are a disaster."

    Of course, he doesn't expound upon it (probably not a need
    except for
    dummies like me). Anyone care to comment? (On the quote,
    not on my
    dumminess...)


    Rich


    Rich Jesse System/Database
    Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech
    International, Sussex, WI USA
    -----Original Message-----
    From: Robson, Peter
    Sent: Monday, October 14, 2002 4:59 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Warehouse design: snowflake vs star schemas


    Just for the record (and perhaps to confirm that there are
    always two sides
    to a story). Readers may like to see the article Chris Date
    wrote to Ralph
    Kemball on the subject of business rules and integrity
    constraints:
    http://www.dbdebunk.com/kimball1.htm
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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).
    --
    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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ora NT DBA
    INET: orantdba_at_netscape.net

    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).
  • Marc Perkowitz at Oct 14, 2002 at 10:09 pm ⇧
    RE: No Nulls? (was: Warehouse design: snowflake vs star schemThis is true. But you still need to add logic to your application to suppress displaying the termination date when it is = "01/01/4000". I can pretty well guarantee your users will not like seeing a "dummy" date on their reports and screens.

    And in this case, you cannot use sysdate, as they will want to see if someone is scheduled for termination in the near future, say for one of the too frequent layoffs that are happening these days.

    You will also need to protect against someone inputting this value or a higher value as that will complicate things.

    The point is that using defaults or dummy values is not a pain-free alternative to nulls. If you do use these, be sure to be consistent and, as usual, document these. Probably a good approach, if you use PL/SQL, is to create a package-level variable that can be used globally. That way you can avoid using hard-coded values. You could also use this approach with languages that are using embedded SQL or ODBC/JDBC. Alternatively, you could use a construct within the language like Java's final static constants.

    Marc Perkowitz

    Original Message -----
    From: Igor Neyman
    To: Multiple recipients of list ORACLE-L
    Sent: Monday, October 14, 2002 4:10 PM
    Subject: Re: No Nulls? (was: Warehouse design: snowflake vs star schem

    Actually, you don't have to deal with "01/01/4000" date (at least on "select"), all you have to do in order find currently employed employees, is:

    where END_EMPLOYMENT > sysdate

    as for inserts, all you have to do, is define "01/01/4000" as a default value for END_EMPLOYMENT,
    also, not allowing NULLs, makes it easier for indexing.

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com


    Original Message -----
    From: Fink, Dan
    To: Multiple recipients of list ORACLE-L
    Sent: Monday, October 14, 2002 4:49 PM
    Subject: RE: No Nulls? (was: Warehouse design: snowflake vs star schem

    The problem I see with NO NULLS is that artificial data must be created, where the data is truly not known. Whether you deal with NULLs or artificial data, you will always have to code accordingly, so it is a wash. Igor's example is an good one. When I write an app to access the END_EMPLOYMENT date, I must handle a date of '01/01/4000'. Or I can handle the NULL condition. As a person who has had to support some very convoluted code, I'd rather deal with NULL. What if the employee record contained TERM_CODE? I would rather have the value NULL, meaning they have not been terminated rather than dealing with hard-coded or lookup values.

    -----Original Message-----
    From: Igor Neyman
    Sent: Monday, October 14, 2002 2:14 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: No Nulls? (was: Warehouse design: snowflake vs star schem

    END_EMPLOYEMENT date for still employed employees equals to "01/01/4000" (or any other pre-defined date in distant future).

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com

    ----- Original Message -----
    From: Adams, Matthew (GECP, MABG, 088130)
    To: Multiple recipients of list ORACLE-L
    Sent: Monday, October 14, 2002 3:39 PM
    Subject: RE: No Nulls? (was: Warehouse design: snowflake vs star schem

    "No application that I can reasonably think of should
    use NULLS, except those pre-81
    where there are obsolete columns."

    Everytime somebody says this to me, I ask them:

    How do you handle still employed employees in an EMPLOYEE table
    that contains a END_EMPLOYEMENT date column?

    What's your take?
    ----
    Matt Adams - GE Appliances - matt.adams_at_appl.ge.com
    Write a poem about a haircut! But lofty, noble, tragic, full of love,
    treachery, retribution, quiet heroism in the face of certain doom!
    Six lines, cleverly rhymed, and every word beginning with the letter s!

    -----Original Message-----
    From: dgoulet_at_vicr.com
    Sent: Monday, October 14, 2002 2:29 PM
    To: 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 ( = NULL or
    != 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

    ____________________Reply Separator____________________
    Author: "Jesse; Rich"
    Date: 10/14/2002 9:33 AM

    On the link below is this quote from C.J.Date:

    "I don't want you to think that my SQL solution to your problem means I
    advocate the use of nulls. Nulls are a disaster."

    Of course, he doesn't expound upon it (probably not a need except for
    dummies like me). Anyone care to comment? (On the quote, not on my
    dumminess...)

    Rich

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
    -----Original Message-----
    From: Robson, Peter
    Sent: Monday, October 14, 2002 4:59 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Warehouse design: snowflake vs star schemas
    >
    >
    Just for the record (and perhaps to confirm that there are
    always two sides
    to a story). Readers may like to see the article Chris Date
    wrote to Ralph
    Kemball on the subject of business rules and integrity constraints: >
    http://www.dbdebunk.com/kimball1.htm
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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).
    --
    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).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Marc Perkowitz
    INET: mperkowitz_at_twjconsulting.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).
  • Jesse, Rich at Oct 14, 2002 at 10:18 pm ⇧
    Thinking about Matt's question, would it be "proper" to move the column to a
    EMP_TERMINATED table with an outer join on EMPNO? There wouldn’t be any
    NULLs...

    Rich

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

    -----Original Message-----
    Sent: Monday, October 14, 2002 4:53 PM
    To: Multiple recipients of list ORACLE-L

    Both would likely do FTS since at any given time more than
    50% of your employees will be current (have an end date
    of 1/1/4000' making it very unlikely that the cbo would choose
    this index. The RBO, would, but it would likely degrade
    not improve your performance.

    John

    Grabowy, Chris wrote:

    Hmmm...but what about the index? Which is faster?


    select * from table where END_EMPLOYMENT IS NULL;


    OR


    select * from table where END_EMPLOYMENT = '01/01/4000';


    I like NULL, but I am leaning towards Igor, and others, to agree upon and
    use a default value, or a "business sense" replacement value for NULL. I
    want to be able to take the awesome advantage of an index...versus FTS?


    Am I headed in the wrong direction?? Any other thoughts??
    -----Original Message-----
    Sent: Monday, October 14, 2002 4:49 PM
    To: Multiple recipients of list ORACLE-L

    The problem I see with NO NULLS is that artificial data must be created,
    where the data is truly not known. Whether you deal with NULLs or artificial
    data, you will always have to code accordingly, so it is a wash. Igor's
    example is an good one. When I write an app to access the END_EMPLOYMENT
    date, I must handle a date of '01/01/4000'. Or I can handle the NULL
    condition. As a person who has had to support some very convoluted code, I'd
    rather deal with NULL. What if the employee record contained TERM_CODE? I
    would rather have the value NULL, meaning they have not been terminated
    rather than dealing with hard-coded or lookup values.
    -----Original Message-----
    Sent: Monday, October 14, 2002 2:14 PM
    To: Multiple recipients of list ORACLE-L

    END_EMPLOYEMENT date for still employed employees equals to "01/01/4000" (or
    any other pre-defined date in distant future).

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com


    Original Message -----
    To: Multiple recipients of list ORACLE-L
    Sent: Monday, October 14, 2002 3:39 PM

    "No application that I can reasonably think of should
    use NULLS, except those pre-81
    where there are obsolete columns."
    Everytime somebody says this to me, I ask them:
    How do you handle still employed employees in an EMPLOYEE table
    that contains a END_EMPLOYEMENT date column?
    What's your take?

    Matt Adams - GE Appliances - matt.adams_at_appl.ge.com
    Write a poem about a haircut! But lofty, noble, tragic, full of love,
    treachery, retribution, quiet heroism in the face of certain doom!
    Six lines, cleverly rhymed, and every word beginning with the letter s!
    -----Original Message-----
    Sent: Monday, October 14, 2002 2:29 PM
    To: Multiple recipients of list ORACLE-L

    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 ( = NULL or
    != 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

    ____________________Reply Separator____________________
    Author: "Jesse; Rich"
    Date: 10/14/2002 9:33 AM

    On the link below is this quote from C.J.Date:
    "I don't want you to think that my SQL solution to your problem means I
    advocate the use of nulls. Nulls are a disaster."
    Of course, he doesn't expound upon it (probably not a need except for
    dummies like me). Anyone care to comment? (On the quote, not on my
    dumminess...)

    Rich

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Susse

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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).
  • Grabowy, Chris at Oct 14, 2002 at 10:24 pm ⇧
    That's not a problem for me...I told all my duh-velopers that RBO was dropped from Oracle8i............(big grin)

    -----Original Message-----
    From: Ora NT DBA
    Sent: Monday, October 14, 2002 5:53 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: No Nulls? (was: Warehouse design: snowflake vs star schem

    Both would likely do FTS since at any given time more than
    50% of your employees will be current (have an end date
    of 1/1/4000' making it very unlikely that the cbo would choose
    this index. The RBO, would, but it would likely degrade
    not improve your performance.

    John

    Grabowy, Chris wrote:

    Hmmm...but what about the index? Which is faster?

    select * from table where END_EMPLOYMENT IS NULL;

    OR

    select * from table where END_EMPLOYMENT = '01/01/4000';

    I like NULL, but I am leaning towards Igor, and others, to agree upon and use a default value, or a "business sense" replacement value for NULL. I want to be able to take the awesome advantage of an index...versus FTS?

    Am I headed in the wrong direction?? Any other thoughts??

    -----Original Message-----
    From: Fink, Dan
    Sent: Monday, October 14, 2002 4:49 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: No Nulls? (was: Warehouse design: snowflake vs star schem

    The problem I see with NO NULLS is that artificial data must be created, where the data is truly not known. Whether you deal with NULLs or artificial data, you will always have to code accordingly, so it is a wash. Igor's example is an good one. When I write an app to access the END_EMPLOYMENT date, I must handle a date of '01/01/4000'. Or I can handle the NULL condition. As a person who has had to support some very convoluted code, I'd rather deal with NULL. What if the employee record contained TERM_CODE? I would rather have the value NULL, meaning they have not been terminated rather than dealing with hard-coded or lookup values.

    -----Original Message-----
    From: Igor Neyman
    Sent: Monday, October 14, 2002 2:14 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: No Nulls? (was: Warehouse design: snowflake vs star schem

    END_EMPLOYEMENT date for still employed employees equals to "01/01/4000" (or any other pre-defined date in distant future).

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com

    ----- Original Message -----
    From: Adams, Matthew (GECP, MABG, 088130)
    To: Multiple recipients of list ORACLE-L
    Sent: Monday, October 14, 2002 3:39 PM
    Subject: RE: No Nulls? (was: Warehouse design: snowflake vs star schem

    "No application that I can reasonably think of should
    use NULLS, except those pre-81
    where there are obsolete columns."

    Everytime somebody says this to me, I ask them:

    How do you handle still employed employees in an EMPLOYEE table
    that contains a END_EMPLOYEMENT date column?

    What's your take?
    ----
    Matt Adams - GE Appliances - matt.adams_at_appl.ge.com
    Write a poem about a haircut! But lofty, noble, tragic, full of love,
    treachery, retribution, quiet heroism in the face of certain doom!
    Six lines, cleverly rhymed, and every word beginning with the letter s!

    -----Original Message-----
    From: dgoulet_at_vicr.com
    Sent: Monday, October 14, 2002 2:29 PM
    To: 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 ( = NULL or
    != 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

    ____________________Reply Separator____________________
    Author: "Jesse; Rich"
    Date: 10/14/2002 9:33 AM

    On the link below is this quote from C.J.Date:

    "I don't want you to think that my SQL solution to your problem means I
    advocate the use of nulls. Nulls are a disaster."

    Of course, he doesn't expound upon it (probably not a need except for
    dummies like me). Anyone care to comment? (On the quote, not on my
    dumminess...)

    Rich

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
    -----Original Message-----
    From: Robson, Peter
    Sent: Monday, October 14, 2002 4:59 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Warehouse design: snowflake vs star schemas
    >
    >
    Just for the record (and perhaps to confirm that there are
    always two sides
    to a story). Readers may like to see the article Chris Date
    wrote to Ralph
    Kemball on the subject of business rules and integrity constraints: >
    http://www.dbdebunk.com/kimball1.htm
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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).
    --
    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).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Grabowy, Chris
    INET: cgrabowy_at_fcg.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).
  • Robson, Peter at Oct 15, 2002 at 11:38 am ⇧
    Yes - the right answer. And can be validated with a little basic
    normalisation.

    peter
    edinburgh

    Thinking about Matt's question, would it be "proper" to move
    the column to a
    EMP_TERMINATED table with an outer join on EMPNO? There
    wouldn’t be any
    NULLs...

    Rich


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

    -----Original Message-----
    Sent: Monday, October 14, 2002 4:53 PM
    To: Multiple recipients of list ORACLE-L


    Both would likely do FTS since at any given time more than
    50% of your employees will be current (have an end date
    of 1/1/4000' making it very unlikely that the cbo would choose
    this index. The RBO, would, but it would likely degrade
    not improve your performance.

    John

    Grabowy, Chris wrote:

    Hmmm...but what about the index? Which is faster?

    select * from table where END_EMPLOYMENT IS NULL;

    OR

    select * from table where END_EMPLOYMENT = '01/01/4000';

    I like NULL, but I am leaning towards Igor, and others, to
    agree upon and
    use a default value, or a "business sense" replacement value
    for NULL. I
    want to be able to take the awesome advantage of an
    index...versus FTS?

    Am I headed in the wrong direction?? Any other thoughts??
    -----Original Message-----
    Sent: Monday, October 14, 2002 4:49 PM
    To: Multiple recipients of list ORACLE-L


    The problem I see with NO NULLS is that artificial data must
    be created,
    where the data is truly not known. Whether you deal with
    NULLs or artificial
    data, you will always have to code accordingly, so it is a
    wash. Igor's
    example is an good one. When I write an app to access the
    END_EMPLOYMENT
    date, I must handle a date of '01/01/4000'. Or I can handle the NULL
    condition. As a person who has had to support some very
    convoluted code, I'd
    rather deal with NULL. What if the employee record contained
    TERM_CODE? I
    would rather have the value NULL, meaning they have not been
    terminated
    rather than dealing with hard-coded or lookup values.
    -----Original Message-----
    Sent: Monday, October 14, 2002 2:14 PM
    To: Multiple recipients of list ORACLE-L


    END_EMPLOYEMENT date for still employed employees equals to
    "01/01/4000" (or
    any other pre-defined date in distant future).

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com



    ----- Original Message -----
    To: Multiple recipients of list ORACLE-L
    Sent: Monday, October 14, 2002 3:39 PM


    "No application that I can reasonably think of should
    use NULLS, except those pre-81
    where there are obsolete columns."
    Everytime somebody says this to me, I ask them:
    How do you handle still employed employees in an EMPLOYEE table
    that contains a END_EMPLOYEMENT date column?
    What's your take?
    ----
    Matt Adams - GE Appliances - matt.adams_at_appl.ge.com
    Write a poem about a haircut! But lofty, noble, tragic, full of love,
    treachery, retribution, quiet heroism in the face of certain doom!
    Six lines, cleverly rhymed, and every word beginning with the
    letter s!
    -----Original Message-----
    Sent: Monday, October 14, 2002 2:29 PM
    To: Multiple recipients of list ORACLE-L


    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 ( = NULL or > != 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
    ____________________Reply Separator____________________
    Author: "Jesse; Rich"
    Date: 10/14/2002 9:33 AM
    On the link below is this quote from C.J.Date:
    "I don't want you to think that my SQL solution to your
    problem means I
    advocate the use of nulls. Nulls are a disaster."
    Of course, he doesn't expound upon it (probably not a need except for
    dummies like me). Anyone care to comment? (On the quote, not on my
    dumminess...)


    Rich


    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Susse
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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).
    This e-mail message, and any files transmitted with it, are
    confidential and intended solely for the use of the addressee. If
    this message was not addressed to you, you have received it in error
    and any copying, distribution or other use of any part of it is
    strictly prohibited. Any views or opinions presented are solely those
    of the sender and do not necessarily represent those of the British
    Geological Survey. The security of e-mail communication cannot be
    guaranteed and the BGS accepts no liability for claims arising as a
    result of the use of this medium to transmit messages from or to the
    BGS. The BGS cannot accept any responsibility for viruses, so please

    scan all attachments. http://www.bgs.ac.uk
    *********************************************************************

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Robson, Peter
    INET: pgro_at_bgs.ac.uk

    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).
  • Toepke, Kevin M at Oct 15, 2002 at 12:28 pm ⇧
    Use your friendly FBI (function-based indexes)


    create index my_fbi on my_table (

    nvl(end_employment, TO_DATE('01-jan-4000'));


    Then the following select will use the index.
    select * from table where NVL(END_EMPLOYMENT, TO_DATE('01-jan-4000') =
    to_date('01-jan-4000');



    -----Original Message-----
    Sent: Monday, October 14, 2002 5:11 PM
    To: Multiple recipients of list ORACLE-L

    Hmmm...but what about the index? Which is faster?


    select * from table where END_EMPLOYMENT IS NULL;


    OR


    select * from table where END_EMPLOYMENT = '01/01/4000';


    I like NULL, but I am leaning towards Igor, and others, to agree upon and
    use a default value, or a "business sense" replacement value for NULL. I
    want to be able to take the awesome advantage of an index...versus FTS?


    Am I headed in the wrong direction?? Any other thoughts??

    -----Original Message-----
    Sent: Monday, October 14, 2002 4:49 PM
    To: Multiple recipients of list ORACLE-L

    The problem I see with NO NULLS is that artificial data must be created,
    where the data is truly not known. Whether you deal with NULLs or artificial
    data, you will always have to code accordingly, so it is a wash. Igor's
    example is an good one. When I write an app to access the END_EMPLOYMENT
    date, I must handle a date of '01/01/4000'. Or I can handle the NULL
    condition. As a person who has had to support some very convoluted code, I'd
    rather deal with NULL. What if the employee record contained TERM_CODE? I
    would rather have the value NULL, meaning they have not been terminated
    rather than dealing with hard-coded or lookup values.

    -----Original Message-----
    Sent: Monday, October 14, 2002 2:14 PM
    To: Multiple recipients of list ORACLE-L

    END_EMPLOYEMENT date for still employed employees equals to "01/01/4000" (or
    any other pre-defined date in distant future).


    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com




    Original Message -----
    To: Multiple recipients of list ORACLE-L
    Sent: Monday, October 14, 2002 3:39 PM

    "No application that I can reasonably think of should
    use NULLS, except those pre-81
    where there are obsolete columns."

    Everytime somebody says this to me, I ask them:

    How do you handle still employed employees in an EMPLOYEE table
    that contains a END_EMPLOYEMENT date column?

    What's your take?

    Matt Adams - GE Appliances - matt.adams_at_appl.ge.com
    Write a poem about a haircut! But lofty, noble, tragic, full of love,
    treachery, retribution, quiet heroism in the face of certain doom!
    Six lines, cleverly rhymed, and every word beginning with the letter s!

    -----Original Message-----

    Sent: Monday, October 14, 2002 2:29 PM
    To: Multiple recipients of list ORACLE-L

    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 ( = NULL or
    != 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

    ____________________Reply Separator____________________
    Author: "Jesse; Rich"
    Date: 10/14/2002 9:33 AM

    On the link below is this quote from C.J.Date:

    "I don't want you to think that my SQL solution to your problem means I
    advocate the use of nulls. Nulls are a disaster."

    Of course, he doesn't expound upon it (probably not a need except for
    dummies like me). Anyone care to comment? (On the quote, not on my
    dumminess...)

    Rich

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
    -----Original Message-----
    From: Robson, Peter [ mailto:pgro_at_bgs.ac.uk ]
    Sent: Monday, October 14, 2002 4:59 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Warehouse design: snowflake vs star schemas


    Just for the record (and perhaps to confirm that there are
    always two sides
    to a story). Readers may like to see the article Chris Date
    wrote to Ralph
    Kemball on the subject of business rules and integrity constraints:
    http://www.dbdebunk.com/kimball1.htm
    <http://www.dbdebunk.com/kimball1.htm>

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    <http://www.orafaq.com>
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    <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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    <http://www.orafaq.com>
    --
    Author:
    INET: dgoulet_at_vicr.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    <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).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Toepke, Kevin M
    INET: ktoepke_at_trilegiant.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).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 14, '02 at 6:28p
activeOct 15, '02 at 12:28p
posts16
users11
websiteoracle.com

11 users in discussion

Igor Neyman: 3 posts Marc Perkowitz: 2 posts Jesse, Rich: 2 posts Grabowy, Chris: 2 posts Fink, Dan: 1 post DENNIS WILLIAMS: 1 post Toepke, Kevin M: 1 post Robson, Peter: 1 post Dgoulet_at_vicr.com: 1 post Adams, Matthew (GECP, MABG, 088130): 1 post Ora NT DBA: 1 post

Content

People

Support

Translate

site design / logo © 2022 Grokbase