Replace NULL value with 0 in query
-
I use dynamic SQL in stored procedure to calculate values and update a table
SET @SQLStatement = 'UPDATE CAV_CalcValues '
SET @SQLStatement = @SQLStatement + 'SET '+@CAV_Column + '= '+@Formula
SET @SQLStatement = @SQLStatement + ' FROM KRE_KeyRatioEuro KRE INNER JOIN CAV_CalcValues CAV'
SET @SQLStatement = @SQLStatement + ' ON KRE.KRE_COM_id = CAV.CAV_COM_id AND KRE.KRE_TIM_id = CAV.CAV_TIM_id'
SET @SQLStatement = @SQLStatement + ' JOIN KRX_KeyRatioXXEuro KRX ON KRE.KRE_COM_id = KRX.KRX_COM_id '
SET @SQLStatement = @SQLStatement + ' WHERE KRE.KRE_COM_id IN ('+@CompanyId+')'
SET @SQLStatement = @SQLStatement + ' AND KRX.KRX_COM_id IN ('+@CompanyId+')'
SET @SQLStatement = @SQLStatement + ' AND KRE.KRE_quarter = 0'
EXEC(@SQLStatement)
Basically I have an Update clause where @Formula
can look like the following:
((KRE_MED_181)-(KRE_MED_14)-(KRE_MED_17))*(1-(KRX_MED_1))+(-(CAV_MED_199)-(KRE_MED_15)-(CAV_MED_200))
(different columns in different tables)
The problem is that in the tables from where I take the values the values are allowed to be NULL but in formulas like above the resulting value will be set to NULL if only one value is NULL and I do not want that. Instead I want a null value be set to 0 so I get a value from the rest of the values in the formula.
(13 + NULL + 26)= NULL
But instead I want NULL=0
(13 + 0 + 26)= =39
I know I can change the formula with a lot of CASE clauses to eliminate the NULL value and replace with 0. But I have like 200 formulas and it would take a long time to accomplish that.
My question is. Is it possible to set something that automatically will replace a NULL value with a 0 in a stored procedure?
-
Use IsNull(<ColumnName>,0)
SJTerrill
-
Well I think you propose to change the formulas as shown in the example. That solution I know. But I was thinking of a way to avoid that, if it is possible
The formula would look like:
(ISNULL((KRE_MED_181),0)-ISNULL(((KRE_MED_14),0)-ISNULL(((KRE_MED_17),0))*(1-ISNULL(((KRX_MED_1),0))+(-ISNULL(((CAV_MED_199),0)-ISNULL(((KRE_MED_15),0)-ISNULL(((CAV_MED_200),0))
-
Sometimes formulas get complicated. However, if you think you are going to use this over again somewhere in your application (and when have we not reused code), then I'm thinking a function could be created and called by passing the parms into it. Just a thought.
-
There would definitely be even more work involved, but could it also make sense to break your formulas up into smaller chunks for readability? Or you could slice up your different formulas into functions as rcnelsonmba notes.
If you don't have control over the source data (not allowing NULLs in the first place), I don't really know of a 'quick' work-around that wouldn't end up looking even uglier.
SJTerrill
-
Might be worth considering placing a DEFAULT value of 0 on your column, otherwise you'll face the problem you know have.
Another approach might be to use COALESCE() like this
SET NOCOUNT ON
CREATE TABLE AveragingMultipleColumns
(year0 DECIMAL(8,5), year1 DECIMAL(8,5), year2 DECIMAL(8,5))
INSERT INTO AveragingMultipleColumns (year0, year1,year2) VALUES(1,2,3)
INSERT INTO AveragingMultipleColumns (year0, year1,year2) VALUES(1,2,NULL)
INSERT INTO AveragingMultipleColumns (year0, year1,year2) VALUES(1,NULL,3)
INSERT INTO AveragingMultipleColumns (year0, year1,year2) VALUES(1,NULL,NULL)
INSERT INTO AveragingMultipleColumns (year0, year1,year2) VALUES(NULL,2,NULL)
INSERT INTO AveragingMultipleColumns (year0, year1,year2) VALUES(NULL,NULL,3)
INSERT INTO AveragingMultipleColumns (year0, year1,year2) VALUES(NULL,NULL,NULL)
SELECT year0, year1, year2,
CASE WHEN (year0 + year1 + year2) IS NOT NULL --most likely one first
THEN (year0 + year1 + year2)/3.0
WHEN (year0 + year1) IS NOT NULL
THEN (year0 + year1)/2.0
WHEN (year0 + year2) IS NOT NULL
THEN (year0 + year2)/2.0
WHEN (year1 + year2) IS NOT NULL
THEN (year1 + year1)/2.0
ELSE COALESCE (year0, year1, year2) END AS average --nulls and singletons
FROM AveragingMultipleColumns
DROP TABLE AveragingMultipleColumns
SET NOCOUNT OFF
year0 year1 year2 average
---------- ---------- ---------- ----------------
1.00000 2.00000 3.00000 2.00000000
1.00000 2.00000 NULL 1.50000000
1.00000 NULL 3.00000 2.00000000
1.00000 NULL NULL 1.00000000
NULL 2.00000 NULL 2.00000000
NULL NULL 3.00000 3.00000000
NULL NULL NULL NULL
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
It appears that you only have a couple of options. You can:
1. Change the source table to disallow Nulls, or somehow update them to 0 or default them to 0.
2. Change your application (not sure what creates your @formula variable) to include the ISNULL or COALESCE function.
3. Write some sort of intermediary function or proc that takes your @formula and parses it apart and then reparses it together with the approriate wrapper ISNULL functions. Example:
EXEC s_WrapFormulaWithIsNull ('((KRE_MED_181)-(KRE_MED_14)-(KRE_MED_17))*(1-(KRX_MED_1))+(-(CAV_MED_199)-(KRE_MED_15)-(CAV_MED_200)) '), @s-2 OUT
SELECT @s-2
with the result being:
(ISNULL((KRE_MED_181),0)-ISNULL(((KRE_MED_14),0)-ISNULL(((KRE_MED_17),0))*(1-ISNULL(((KRX_MED_1),0))+(-ISNULL(((CAV_MED_199),0)-ISNULL(((KRE_MED_15),0)-ISNULL(((CAV_MED_200),0))
or something like that
David -
I'm suprised that noone has suggested using COALESCE. This uses the first non null value in a list of values. So how about replacing
-ISNULL(((KRE_MED_14),0)
with
-COALESCE(KRE_MED_14,0) etc.
-
I did 
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
So did I?
David -
So sorry
So you did.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply