SQL Server Performance

Replace NULL with 0 in aquery

Discussion in 'General Developer Questions' started by tmarko, Jan 22, 2004.

  1. tmarko New Member

    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 to replace 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, if it possible to in the stored procedure set something that automatically will replace a NULL value with a 0 in an easy way?
  2. Luis Martin Moderator

    As far I know there is nothing to set automatically, but wait for developer experts oppinions.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  3. frettmaestro New Member

    This will do the trick:

    SELECT IsNull(Field1, 0) + IsNull(Field2, 0)

    --
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand"
  4. FrankKalis Moderator

    Does this give you some ideas?


    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


    Frank
    http://www.insidesql.de
    http://www.familienzirkus.de
  5. FrankKalis Moderator

    Sorry, forgot the resultset looks like


    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
    http://www.insidesql.de
    http://www.familienzirkus.de
  6. frettmaestro New Member

    No offence Frank, your solution will most definitely work, but I find it to be unnecessarily complicated for the task at hand. The IsNull built-in function works perfectly for this purpose:

    (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))

    --
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand"
  7. Luis Martin Moderator

    I think Tmarko was looking for a automatic way.
    He said there is 200 formulas and he know how to change using Case or any of your solution.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  8. tmarko New Member

    Yes as LuisMartin write. I would like something automatic in my stored procedure. <br /><br />I really wanted to be sure that nothing else can be done but changing the formulas to deal with this problem. That no secret way can be done. My users have asked for this and asked about the time to fixed this. So if 200 formulas need to be updated I have to say that it will take some time to go through those.<br /><br />Thnaks anyway and I feel more comfortable to say the truth to my users <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  9. frettmaestro New Member

    Ok, I missed the part about the 200 formulas actually, I must read more carefully the next time I guess <img src='/community/emoticons/emotion-5.gif' alt=';)' /> Something which doesn't actually sound applicable in this scenario but I find worth mentioning is to set a default value of 0 in the database and disallow NULLS. I am aware that you said NULLS are allowed and I don't know the nature of you datasource, but in an isolated case this would solve your problem in a minimal amount of time.<br /><br />--<br />Frettmaestro<br />"Real programmers don't document, if it was hard to write it should be hard to understand"
  10. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by frettmaestro</i><br /><br />No offence Frank, your solution will most definitely work, but I find it to be unnecessarily complicated for the task at hand. The IsNull built-in function works perfectly for this purpose:<br /><br />(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))<br /><br />--<br />Frettmaestro<br />"Real programmers don't document, if it was hard to write it should be hard to understand"<br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br />No harm taken [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />As in most cases, there is more than one way to skin a cat. <br />Even better than dealing with NULL logic in calculations, is having a DEFAULT of 0 on those columns, when you know for sure you are going to calculate on them.<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
  11. tmarko New Member

    No 0 is not the same as NULL in the source tables. Because a NULL value represent that the value does not exist for that specific report in my case. Hence the value can exist for an other report. But a value can be 0 and so forth exist for the report. So to replace all NULL values with 0 is not a possible solution. What is irritating is that a single NULL value in a formula destroys the whole formula.

    Interesting that when using

    SELECT SUM(KRE_MED_1) FROM ...
    returns something if at least one value is <> NULL
    But

    SELECT ((KRE_MED_1)+(KRE_MED_2)+(KRE_MED_3)) FROM ....

    Does not work if a single value is NULL

    I think I need to update the formulas

    Great forum, and great discussions!
  12. FrankKalis Moderator

  13. tmarko New Member

    Well FrankKalis. I agree with you, but Microsoft should implement something that can be set in a Stored Procedure that make it possible to ignore NULL and set them to 0 if I would like that so adding values with a possible NULL value won't destroy the calculation.
  14. FrankKalis Moderator

    I guess that wouldn't be ANSI SQL 92 compliant, where SQL Server does quite a good implementation.<br /><br />Consider this just a WAG [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Might SET CONCAT_NULL_YIELDS_NULL help you ?<br /><br /><br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
  15. frettmaestro New Member

    tmarko: search & raplace with regular expressions can work miracles <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />--<br />Frettmaestro<br />"Real programmers don't document, if it was hard to write it should be hard to understand"
  16. Luis Martin Moderator

    Indeed Frett.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  17. Negative New Member

    Frank,

    I don't think CONCAT_NULL_YIELDS_NULL will help in this situation. That only works for string concatenation, not with math functions.

    Example:

    SET CONCAT_NULL_YIELDS_NULL OFF
    SELECT NULL + 5

    returns NULL

    SET CONCAT_NULL_YIELDS_NULL OFF
    SELECT NULL + '5'

    returns '5'

    I think the only option in this case is the change the queries using CASE, ISNULL, or COALESCE.
  18. tmarko New Member

    Yes tried

    SET CONCAT_NULL_YIELDS_NULL OFF

    No change

    Thanks for the enthusiasm in this forum
  19. FrankKalis Moderator

    Well, as I wrote, consider it a WAG. <br />To be honest, I would have been suprised, but willing to learn each day something new [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
  20. jongalloway New Member

    You want to coalesce each value with 0, as in:
    ((COALESCE(KRE_MED_181,0))-((COALESCE(KRE_MED_14,0))-((COALESCE(KRE_MED_17,0)))*(1-((COALESCE(KRX_MED_1,0)))+(-((COALESCE(CAV_MED_199,0))-((COALESCE(KRE_MED_15,0))-((COALESCE(CAV_MED_200,0)))
    There are two ways you can do that - string parse the formula and find variables (text which isn't ()+- ) and replace variable with COALESCE(variable,0).

    The other way is to get your formula fields from the database schema. I believe this will work, although I don't have access to your tables to test:

    declare @formula varchar(200)
    set @formula = '((KRE_MED_181)-(KRE_MED_14)-(KRE_MED_17))*(1-(KRX_MED_1))+(-(CAV_MED_199)-(KRE_MED_15)-(CAV_MED_200))'

    declare @column_name varchar(100)

    DECLARE columns_cursor CURSOR FOR
    select column_name from information_schema.columns where table_name in ('KRE_KeyRatioEuro','CAV_CalcValues CAV','KRX_KeyRatioXXEuro')

    OPEN columns_cursor

    FETCH NEXT FROM columns_cursor
    INTO @column_name

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @formula = REPLACE(@formula,@column_name,'COALESCE(' + @column_name + ',0)')
    END

    CLOSE columns_cursor
    DEALLOCATE columns_cursor
    GO
  21. jongalloway New Member

    oops, missed fetch next in cursor loop:

    declare @formula varchar(200)
    set @formula = '((KRE_MED_181)-(KRE_MED_14)-(KRE_MED_17))*(1-(KRX_MED_1))+(-(CAV_MED_199)-(KRE_MED_15)-(CAV_MED_200))'

    declare @column_name varchar(100)

    DECLARE columns_cursor CURSOR FOR
    select column_name from information_schema.columns where table_name in ('KRE_KeyRatioEuro','CAV_CalcValues CAV','KRX_KeyRatioXXEuro')

    OPEN columns_cursor

    FETCH NEXT FROM columns_cursor INTO @column_name

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @formula = REPLACE(@formula,@column_name,'COALESCE(' + @column_name + ',0)')
    FETCH NEXT FROM columns_cursor INTO @column_name
    END

    CLOSE columns_cursor
    DEALLOCATE columns_cursor
    GO

Share This Page