Re: Q: Suppressing NULLs using Forms 4.5
Date: 1997/06/06
Message-ID: <33985b36.5411671_at_www.sigov.si>#1/1
On 05 Jun 1997 15:29:58 GMT, andrewto_at_kingfish.cse.tek.com (Andrew Tompkins) wrote:
>In article <33949800.49219898_at_www.sigov.si>
> jurij.modic_at_mf.sigov.mail.si (Jurij Modic) writes:
>
>>
>> I can't see how CHR(0) or space can be of more help then NULL in IF or
>> WHILE statements. In fact, it can give you wrong ansver while NULL
>> can't. What exactly are this nastiness?
>>
>> And wath do you replace nulls in you numeric columns with?
>>
>
>Sorry. Guess my email didn't make it. CHR(0) and space will be counted
>in a COUNT(*) type query while NULL will not. We use a lot of COUNT(*)
>queries in our system so we defined all of the columns in all of our tables
>as NOT NULL and gave the VARCHAR2 and DATE columns a default value of
>' ' initially, then chr(0), and the NUMBER columns a default of 0.
>
>Also, conditions for 'if' and 'while' statements return 3 values (TRUE,
>FALSE and NULL) rather than the standard 2 (TRUE and FALSE). NULL is
>always treated like FALSE where, in many cases, the developer may want
>if treated like TRUE or as a third possibility. Not using NULL means
>we don't have to check for and deal with it constantly.
>
>--Andy
Hi, Andy
I don't know about e-mail, it has been out of my reach last week :-(
[Quoted] Now, about COUNT(*) queries and NULL values - I think you are wrong here. As a matter of fact, all nulls are counted in this sort of queries. However, if you do COUNT(column_with_nulls) then only non-null values vill be counted.
Example:
SQL> CREATE TABLE test (dummy VARCHAR2(10) NULL);
Table created.
SQL> INSERT INTO test (dummy) VALUES (NULL); 1 row created.
SQL> SELECT COUNT(*), COUNT(dummy) FROM test;
COUNT(*) COUNT(DUMMY)
--------- ------------
1 0
1 row selected.
[Quoted] So if COUNT(*) queries were the main reason for your decision to define all columns as NOT NULL, then there was no need to do so. The very idea to have no NULL column in the database seems very strange to me, but it is a matter of opinions. I just find it hard to understand that in your application's real life you could treat numeric 0 values equaly to unknown values (NULLs). I also doubt you could assign default value of ' ' or CHR(0) to date column !?
About NULLs in IF and WHERE statements - even if you code your "unknown values" with constant values of your choice (let us say with CHR(0), 0 and very_old_date) there are still many situations where you must treat this exceptional values separately to get proper results. Just think about conditions with < or > operators. So your developers must still have in mind this "unknown values" and have to code "exceptionalconditions" when needed. With this in mind I realy don't see any benefit in this concept as oposed to NULL concept. In fact, I see more drawbacks then improvements.
I think NULLs are here for very good reason - to represent unknown values in real life and in my opinion the whole logic associated with them is in most cases the best it could be. And if we, developers, sometimes have troubles with its logic, I don't think this could be excuse to treat very natural things ("unknown values") unnaturaly just to make our lifes easier.
Regards,
Jurij Modic Republic of Slovenia tel: +386 61 178 55 14 Ministry of Finance fax: +386 61 21 45 84 Zupanciceva 3e-mail: jurij.modic_at_mf.sigov.mail.si Ljubljana 1000
Received on Fri Jun 06 1997 - 00:00:00 CEST