Prev: Speed in select statement
Next: Backup issue
From: Jay Konigsberg on 19 Apr 2010 09:47
> Create the Order field with a default value of 0.

I actually like this better than ISNULL, or COALESCE because you're defining
the data in the table and letting the engine enforce the scope. Using
COALESCE (in this case) is just picking up the mess after it's made.

--
Jay Konigsberg
SQL Server DBA in Sacramento, CA
http://www.linkedin.com/in/jaykonigsberg

Live in Sacramento, CA?
Join the Sacramento SQL Server User Group on LinkedIn
http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg



"David Kaye" <sfdavidkaye2(a)yahoo.com> wrote in message
news:hq0e0v$hvl$2(a)news.eternal-september.org...
> "jpBless" <jp3blessNoSpam(a)hotmail.com> wrote:
>
>>My problem is this field sometimes returns Null When Values in "Order" is
>>null. I would like to have 0 instead of Null
>
> Create the Order field with a default value of 0.
>


From: bill on 19 Apr 2010 13:24
Be careful about where you put the ISNULL or the COALESCE function.
For instance, if the only null column is Order.Other, do you want the
entire calculation to return zero, or do you want to return the sum of
all the non-null terms?

If you want the entire calculation to return zero, then just wrap the
entire thing in an ISNULL or a COALESCE. If you want to get the sum
of whatever actually exists, then you need to put an ISNULL or
COALESCE on each term so that they can all add, regardless of which
columns are null.

Jay posts a lot of excellent ideas to these groups, but I'm not sure I
agree on the default value here. For one thing, if you do an OUTER
JOIN, you may generate nulls regardless of defaults (becuase of
missing rows). Also, 0 (zero) tax is different than a null in the tax
column. A default of zero masks this difference.

Thanks,

Bill
From: jpBless on 24 Apr 2010 09:45
Thanks Bill... well noted

"bill" <billmaclean1(a)gmail.com> wrote in message
news:0a90193d-0240-436e-859c-2ac88e024ebe(a)b33g2000yqc.googlegroups.com...
> Be careful about where you put the ISNULL or the COALESCE function.
> For instance, if the only null column is Order.Other, do you want the
> entire calculation to return zero, or do you want to return the sum of
> all the non-null terms?
>
> If you want the entire calculation to return zero, then just wrap the
> entire thing in an ISNULL or a COALESCE. If you want to get the sum
> of whatever actually exists, then you need to put an ISNULL or
> COALESCE on each term so that they can all add, regardless of which
> columns are null.
>
> Jay posts a lot of excellent ideas to these groups, but I'm not sure I
> agree on the default value here. For one thing, if you do an OUTER
> JOIN, you may generate nulls regardless of defaults (becuase of
> missing rows). Also, 0 (zero) tax is different than a null in the tax
> column. A default of zero masks this difference.
>
> Thanks,
>
> Bill


First  |  Prev  | 
Pages: 1 2
Prev: Speed in select statement
Next: Backup issue