Re: Problem with NULL values

"Obnoxio The Clown" <obnoxio@hotmail.com> writes: > From: Thomas Parsli <thomas.parsli@startsiden.no> > > > >"Obnoxio The Clown" <obnoxio@hotmail.com> writes: > > > > > From: Thomas Parsli <thomas.parsli@startsiden.no> > > > > > > > >Carlos Benjamin <benj@linuxstart.com> writes: > > > > > > > > > Colin Thu <ly-cong.thu@pp.inet.fi> wrote: > > > > > > > > > > >I have some problem with my Query (SQL). It's about NULL values. > > > > > > > > > > > >SELECT ordernro, quantity, sent, (quantity - sent ) as 'q_left' > > > > > >FROM orders> > > > > > > > > > > >So, If sent = NULL then (quatity - sent) is not "quantity". It'll > > > >become > > > > > >NULL too. So, I need the real value for "q_left". > > > > > > > > > > Hate it when that happens. > > > > > > > > > > Unless you have some compelling reason to have nulls in there, I'd > >fix > > > >those columns to disallow nulls and change your application to default > >to > > > >zeroes. Then update the table to set sent to 0 where sent is null. > > > > > > > >Fields like this deserves a DEFAULT value _and_ a NOT NULL. > > > >And I'd rather use -2,147,483,647 as an indicator of "unknown" than > > > >NULL... > > > > > > And your reason for this is....? > > > >I don't like NULLS? -The reasons for this I don't know;) > > Allow me to clarify: "And you reason for using such a bizarre number as a > NULL is? As a SELECT SUM() or SELECT (quantity - sent) will give some > intriguing results.... Point taken mr clown... I can see that I should have written "I'd rather have my nose-hairs cut with [INSER HUGE OBJECT HERE] than playing with NULLs" The reason I don't like NULLs are mainly that sometimes you don't always know what happens with them (Informix looks decent there) and -the most important one- people sometime forgets to insert values there so it ends up as a NULL when it should have been something else. I've had some bad experiences with NULLs on older RDBMS (like Ingres) and should probably not judge Informix by that... Thomas

From: Thomas Parsli <thomas.parsli@startsiden.no> > >"Obnoxio The Clown" <obnoxio@hotmail.com> writes: > > > From: Thomas Parsli <thomas.parsli@startsiden.no> > > > > > >Carlos Benjamin <benj@linuxstart.com> writes: > > > > > > > Colin Thu <ly-cong.thu@pp.inet.fi> wrote: > > > > > > > > >I have some problem with my Query (SQL). It's about NULL values. > > > > > > > > > >SELECT ordernro, quantity, sent, (quantity - sent ) as 'q_left' > > > > >FROM orders> > > > > > > > > >So, If sent = NULL then (quatity - sent) is not "quantity". It'll > > >become > > > > >NULL too. So, I need the real value for "q_left". > > > > > > > > Hate it when that happens. > > > > > > > > Unless you have some compelling reason to have nulls in there, I'd >fix > > >those columns to disallow nulls and change your application to default >to > > >zeroes. Then update the table to set sent to 0 where sent is null. > > > > > >Fields like this deserves a DEFAULT value _and_ a NOT NULL. > > >And I'd rather use -2,147,483,647 as an indicator of "unknown" than > > >NULL... > > > > And your reason for this is....? > >I don't like NULLS? -The reasons for this I don't know;) Allow me to clarify: "And you reason for using such a bizarre number as a NULL is? As a SELECT SUM() or SELECT (quantity - sent) will give some intriguing results.... ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com