John W. Colby
jwcolby at colbyconsulting.com
Sat Mar 12 12:36:29 CST 2005
LOL. ARTHUR! I have simply started my own round table to deal with those who cling to simplistic ideas at the expense of reality. Null = "I don't know" "I don't know" = "I don't know" "I don't know" = NULL I am NOT, nor have I ever espoused using a ZERO (or any other VALUE) for a null. I AM and I espouse using a record that specifically says "I don't know". I do so for a very specific reason, as a workaround to a common problem; We are trying to select a value from a combo to fill in a field. Suppose that you have a person who has a valid address. They live in California. You select California as their state. Suppose you discover that person no longer lives at that address but you DON'T KNOW where she lives? What do you do? You have four choices: 1) Delete the person from your database 2) Ignore the fact that the person does not live at the old address and just leave the old address in there. 3) Create a "I don't know" answer in the state table. 4) Add code to every such combo, use a UNION to get an "I don't know" in the combo, then add code to delete out the existing value in the field when that "I don't know" is selected. Can you say Mickey Mouse? Choice three exactly and completely correlates to, corresponds to, replaces a null value. Null means "I don't know". My "zeroth record" means "I don't know". Now one of our esteemed colleagues made some silly claim several years ago that null could mean many different things, such as "I never got the information" or "I had to delete the information" or ... Hmmm it was so silly I didn't follow the logic any further. In any event, once stored in a table, a null means nothing more than "I don't know". So, Arthur of the Foolish Clan, I simply place a record in my database that explicitly means "I don't know". That RECORD replaces the null value. If you need to explicitly denote "I don't know" when asked what color eyes, what state, what model car, what ... (insert your favorite question / lookup table here) simply select the "I don't know" answer from the required table. Where your confusion comes from is the fact that I insert this record as the "zeroth" value in the PK field. I am NOT equating a zero to NULL, I am creating an "I don't know" record at PKID value zero! I do that as a convenience. Access (and SQL Server IIRC) start their identity value with 1 and work up if it is a linear ID. That leaves a "hole" at the zero value where I can insert a record. I literally append a record into the table with a ZERO for the PK (I always use an autonumber as the PK) and fill in "I don't know" for the value (if the value displayed is a piece of text). If having a null is so danged important to you, then insert a null in the value. I find that having the text "I don't know" tells the user that this is the "I don't know" record but you are the master of your databases. Nulls stored in PK values can work to your advantage, or be a royal PITA. Nulls cause inner joins on that field to fail to pull a record. If you want to pull a value you now have to (remember to) use an outer join. That's fine if you KNOW that there will be nulls but it is just a PITA to me. I have to assume that you use outer joins everywhere since any column may have nulls in them? At any rate, you can see that I do NOT equate a zero to a null. I know perfectly well that a null is not a zero, zeros have a specific meaning and NULLS have a specific meaning. And I understand joins perfectly well thank you. I am NOT going to apply for membership back in to your round table! I have my OWN round table thank you. ;-) John W. Colby www.ColbyConsulting.com Contribute your unused CPU cycles to a good cause: http://folding.stanford.edu/ -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller Sent: Saturday, March 12, 2005 12:47 AM To: dba-sqlserver at databaseadvisors.com Subject: [dba-SQLServer] Was... Update query through Access ADP... extendednow Time to get on my high horse.... I, Arthur, son of Uther Pendragon, defeater of the Saxons, lord over Allllll Briton, do declare... Let us never mistake nulls for zeroes. This is a BAD idea. JFC's zeroth row argument notwithstanding, in fact I have banished him from the roundtable for his insistence on this foolish opinion. Null is null. It is NOT zero. These two values should never be confused, nor should they be conflated by making the default value of a column zero. This is BAD design. Nulls mean "I don't know." Zeroes mean (as in how many children do you have?) zero. Big difference! Plugging zeroes into nulls is BAD BAD BAD. SPANK! Don't do it again or I shall have to send you to a correctional school run by deviant priests. Don't do it! Don't ever do it. Don't believe JC's zero'th row argument, he is deluded and has simply found a comfortable workaround his inability to comprehend joins. That's all there is to it. Nulls are nulls. Zeroes are zeroes. Arthur dmcafee at pacbell.net wrote: ><back pedal mode on> > >WHERE LEN(Contact) = 0 > >would return a null when Contact is null, not a zero. > >the following below will take care of nulls, '', ' ', ' ' and variations >of spaces > > >UPDATE Projects2 >SET ContactEmail = NULL >WHERE ISNULL(LTRIM(RTRIM(@X)),'') = '' > > > >-----Original Message----- >From: dba-sqlserver-bounces at databaseadvisors.com >[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of >dmcafee at pacbell.net >Sent: Friday, March 11, 2005 10:14 AM >To: dba-sqlserver at databaseadvisors.com >Subject: RE: [dba-SQLServer] Update query through Access ADP > > >Steven since you have the answer to your question, I'd like to chime in >to let you know that you can also do something like this: > >UPDATE Projects2 >SET ContactEmail = NULL >WHERE LEN(Contact) = 0 > >D > > >-----Original Message----- >From: dba-sqlserver-bounces at databaseadvisors.com >[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Steve >Erbach >Sent: Friday, March 11, 2005 5:52 AM >To: dba-sqlserver at databaseadvisors.com >Subject: [dba-SQLServer] Update query through Access ADP > > >Dear Group, > >I've been looking through some of a client's ASP application with a >view to making some slight modifications. (There are almost 800 ASP >files. Yikes!) > >The back end is SQL Server and I've been using Access 2003 to construct >views and stored procedures. I've been working on a COPY of the tables >on my own SQL Server rather than the customer's live data. > >What's got me curious is this: I've been working with an sproc that >updates a column containing an email address. It looks something like >this: > >UPDATE Projects2 >SET ContactEmail = NULL >WHERE (Contact = '') OR (Contact = ' ') OR (Contact IS NULL) > >So if the Contact name doesn't have anything in it, I want the >Contact's email address to be NULL. At present, there's a default >e-mail address in every row. > >After the sproc runs I look at the table itself. Well, this table has >133,000 records in it. Access 2003 shows me the first 10,000 by >default. But when I go past the 10,000th record, the e-mail column >still has addresses in it even though the Contact field is empty. > >Is there something I'm missing in the sproc? Something like a command >that says, "OK, I really, REALLY want to update the rows beyond 10,000, >too." > >Regards, > >Steve Erbach >Scientific Marketing >Neenah, WI >www.swerbach.com >Security Page: www.swerbach.com/security >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > > > > > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com