Return 0 (zero) when null
in [Programming SQL]
|
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 |