Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JSG_Martin
Starting Member
6 Posts |
 Posted - 2007-11-01 : 06:24:11
|
| I currently have 5 fields in the same SQL Server table, 4 of which pull Stock Levels from other tables and the 5th is a Total Stock field which is to add the 4 previous fields.The problem is that some of the first 4 fields contain nulls as well as figures and when the Total Stock trys to add these together the outcome is Null, which is incorrect.E.G COL 1 COL 2 COL 3 COL 4 TOTALSTOCK 2 NULL 0 1 NULLI have tried to change it to a NOT NULL Column but won't let me.Is there a way to do an update to change NULL's to 0?Any help would be grateful |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2007-11-01 : 06:37:33
|
| you could either update the null values in the table or just 'swap' them for a 0 in your calculationlook up coalesce in BOLEm |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-01 : 07:13:21
|
| 1 coalesce(col,0) - sql2 IIF(isnull(col),0,col) -- Fornt end VB6MadhivananFailing to plan is Planning to fail |
|
|
|
arorarahul.0688
Posting Yak Master
125 Posts |
Posted - 2007-11-01 : 08:03:06
|
| try thisselect col1 ,col2 ,col3,col4 coalesce(col1,0)+coalesce(col2,0)+coalesce(col3,0),coalesce(col4,0) c5 from ddthis will help you certainlyRahul Arora MCA 07 BatchNCCE Israna, PanipatHRY, INDIA |
|
|
|
JSG_Martin
Starting Member
6 Posts |
Posted - 2007-11-01 : 11:34:23
|
| Thank you all, coalesce worked a treat and thanks for such quick responsesHalf expected to wait a couple days |
|
|
|
|
|
|
|
|
Calculation of fields which contain Nulls