Re: Problem with NULL values

Colin Thu <ly-cong.thu@pp.inet.fi> wrote: >Dear friends! > >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. carlos carlos Currently on hiatus from unemployment. ---------------------- Do you do Linux? :) Get your FREE @linuxstart.com email address at: http://www.linuxstart.com

Carlos Benjamin <benj@linuxstart.com> writes: > Colin Thu <ly-cong.thu@pp.inet.fi> wrote: > > >Dear friends! > > > >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... CREATE TABLE orders ( ... quantity INTEGER NOT NULL DEFAULT 0, ... ) Thomas