are nulls bad

[aaj]

Hi

I am probably going to regret asking this because I'm sure you are going to
tell me my design is bad 8-) ah well we all have to learn....

anyway

I often use Nulls as a marker to see if certain tasks have been completed.

A typical example would be a column say invoice_value

when new work is entered and a new record is appended, I leave the
invoice_value column as NULL, so if I want a View for uninvoiced work I
check for the invoice_value being equal to NULL, and alternatively if I want
invoiced work, I check for not null. I did it this way to save putting in
another column that needed to be set to TRUE or FALSE. (I do similar things
elsewhere aswell)

so far everything seems to work OK but reading some old stuff on Google I
get the feeling that NULLS should be left alone, have I done wrong?

many thanks

Andy

[Hugo Kornelis]

On Tue, 25 May 2004 15:08:27 +0100, aaj wrote:

Hi

I am probably going to regret asking this because I'm sure you are going to
tell me my design is bad 8-) ah well we all have to learn....

anyway

I often use Nulls as a marker to see if certain tasks have been completed.

A typical example would be a column say invoice_value

when new work is entered and a new record is appended, I leave the
invoice_value column as NULL, so if I want a View for uninvoiced work I
check for the invoice_value being equal to NULL, and alternatively if I want
invoiced work, I check for not null. I did it this way to save putting in
another column that needed to be set to TRUE or FALSE. (I do similar things
elsewhere aswell)

so far everything seems to work OK but reading some old stuff on Google I
get the feeling that NULLS should be left alone, have I done wrong?

many thanks

Andy

Hi Andy,

I don't think NULLS are bad. I do think they arer tricky and many errors
have been caused by improper understanding of NULLS and three-valued
logic.

As to your design- I don't think it's bad. In similar cases, I tend to use
a column that stores the date/time a task is finished. And (like you) I
use column_name IS NULL (*not* column_name = NULL!!!!) to find unfinished
work.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Andy Williams]

aaj,

I agree with Hugo, but here's an alternate viewpoint from Aaron's site:

http://www.aspfaq.com/show.asp?id=2073

-Andy

"aaj" <a.*@c.com> wrote in message
news:40**********************@news.easynet.co.uk.. .

Hi

I am probably going to regret asking this because I'm sure you are going to tell me my design is bad 8-) ah well we all have to learn....

anyway

I often use Nulls as a marker to see if certain tasks have been completed.

A typical example would be a column say invoice_value

when new work is entered and a new record is appended, I leave the
invoice_value column as NULL, so if I want a View for uninvoiced work I
check for the invoice_value being equal to NULL, and alternatively if I want invoiced work, I check for not null. I did it this way to save putting in
another column that needed to be set to TRUE or FALSE. (I do similar things elsewhere aswell)

so far everything seems to work OK but reading some old stuff on Google I
get the feeling that NULLS should be left alone, have I done wrong?

many thanks

Andy

[aaj]

Thanks for the replies, I feel a little better

I'll give the link some more study

thanks again

Andy

"aaj" <a.*@c.com> wrote in message
news:40**********************@news.easynet.co.uk.. .

Hi

I am probably going to regret asking this because I'm sure you are going to tell me my design is bad 8-) ah well we all have to learn....

anyway

I often use Nulls as a marker to see if certain tasks have been completed.

A typical example would be a column say invoice_value

when new work is entered and a new record is appended, I leave the
invoice_value column as NULL, so if I want a View for uninvoiced work I
check for the invoice_value being equal to NULL, and alternatively if I want invoiced work, I check for not null. I did it this way to save putting in
another column that needed to be set to TRUE or FALSE. (I do similar things elsewhere aswell)

so far everything seems to work OK but reading some old stuff on Google I
get the feeling that NULLS should be left alone, have I done wrong?

many thanks

Andy