none
Expression Logic - How to get rid of NULL values in expression RRS feed

  • Question

  • I have one computed column in my table, value of computed column is expression logic which depend on other columns.

    Example: [4] = [1] + [2] - ([3]/ ([6] + [5]))

    In this Column [4] value is depended on columns [1], [2], [3], [5] and [6]

    My problem is while creating table, value of any column can be NULL and it results whole expression to be NULL

    example NULL + [3] comes out to be NULL even if [3] has some value like 100.

    I can't take default value of columns to 1 because in that case + operator will not give me right result.

    I also can't take 0 because if it is '\' (divide) it would again give me wrong result.

    one of real time expression I am working on is - 

    ( ( ( ( ( [187] - [106] ) / ([102] + [106]) ) + [5] + [4] + [3] ) + ( ( [187] - [106] ) / [106] ) ) ) * 100

    I need to put some logic while creating table for this computed column and I am not sure how should I deal with this.

    any help would be highly appreciated, thanks in advance.

    Thursday, April 25, 2013 7:28 AM

Answers

  • You need to use CASE WHEN effectively to sort out the issues.

    Please provide your table structure, probabaly will be able to help you better.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, April 25, 2013 7:31 AM

All replies

  • You need to use CASE WHEN effectively to sort out the issues.

    Please provide your table structure, probabaly will be able to help you better.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, April 25, 2013 7:31 AM
  • ( ( ( ( ( ISNULL([187],0) - ISNULL([106],0) ) / (ISNULL([102],0) + ISNULL([106],0)) ) + ISNULL([5],0) + ISNULL([4],0) + ISNULL([3],0) ) + ( ( ISNULL([187],0) - ISNULL([106],0) ) / ISNULL([106],1) ) ) ) * 100

    Try ISNULL(ColName,0) WHEN u have +- Operator 

     ISNULL(ColName,1) WHEN */ Operator.


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you.
    Junaid Hassan.

    Thursday, April 25, 2013 7:46 AM
  • Use

    isnull(col1,0)

    for whatever columns that can be with null values.


    Many Thanks & Best Regards, Hua Min

    Thursday, April 25, 2013 8:04 AM
  • ( ( ( ( ( ISNULL([187],0) - ISNULL([106],0) ) / (ISNULL([102],0) + ISNULL([106],0)) ) + ISNULL([5],0) + ISNULL([4],0) + ISNULL([3],0) ) + ( ( ISNULL([187],0) - ISNULL([106],0) ) / ISNULL([106],1) ) ) ) * 100

    Try ISNULL(ColName,0) WHEN u have +- Operator 

     ISNULL(ColName,1) WHEN */ Operator.


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you.
    Junaid Hassan.

    I proposed Janaid's reply as answer and give you an example. The ISNULL approach also works, but I think the table definition is much cleaner with a CASE expression.

    CREATE TABLE ( col_1 integer NULL , col_2 integer NULL , col_3 integer NULL , col_4 AS
                  CASE WHEN (col_1 IS NULL) OR (col_2 IS NULL) OR (col_3 IS NULL) OR (col_5 IS NULL) OR (col_6 IS NULL)
                          THEN 0                   WHEN col_5 + col_6 = 0 /* This causes a division by zero */
                          THEN 0
    ELSE col_1 + col_2 - (col_3 / (col_5 + col_6)) END , col_5 integer NULL , col_6 integer NULL )



    Thursday, April 25, 2013 8:42 AM
  • thanks for your reply Junaid.

    In case of static formula it would work fine, but in my case I am getting formula from other table, or it would be different every time so I need to make my create table statement or logic for computed column very generic.

    its like 

    declare @dynamiccolumnnames varchar(max) = formula to create string of comma separated column names. while creating this string I am getting formula from other table column.

    create table abc 

    (

    @DynamicColumnNames

    )

    In short formula is not constant, columns in formula are not change and can be change, it can be anything and so I have to make it very generic.

    I would like to thanks again for your help, i would be really grateful if you can suggest something.

    Thursday, April 25, 2013 5:52 PM
  • thanks Chris for your time... 

    I am copying my reply to Junaid here...

    ----------------------------------

    In case of static formula it would work fine, but in my case I am getting formula from other table, or it would be different every time so I need to make my create table statement or logic for computed column very generic.

    its like 

    declare @dynamiccolumnnames varchar(max) = formula to create string of comma separated column names. while creating this string I am getting formula from other table column.

    create table abc 

    (

    @DynamicColumnNames

    )

    In short formula is not constant, columns in formula are not constant and can be change, it can be anything and so I have to make it very generic.

    I would like to thanks again for your help, i would be really grateful if you can suggest something.

    Thursday, April 25, 2013 5:55 PM
  • In last line.. I correct my typo

    columns in formula are not constant and can be change everytime...

    Thursday, April 25, 2013 5:56 PM
  • thanks HuaMin for your reply

    but in this case all columns in denominator will be calculated as 0 and ultimately error...

    example [1]/[2].. if [2] becomes zero it will throw error...

    Thursday, April 25, 2013 5:59 PM
  • Where is the DDL? What is the name of the table? Why do you think that using a digit is a clear, precise name for a data element? 

    >> columns in formula are not constant and can be change every time...<<

    This is not how to use SQL, or any programming language for that matter. Did you ever see the classic Syd Harris cartoon of two mathematicians  looking at a blackboard that has a blob in the middle of the formulas that say "Here a miracle occurs!"  The first character is saying,  "You need to be more explicit in the second step". 

    What you have is an "Automobiles, Squids and Lady Gaga" function. Plese stop programming.  


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, April 25, 2013 6:19 PM
  • I wouldn't put it as strongly as Mr. CELKO, but he has a point when he says that you have an "Automobiles, Squids and Lady Gaga" function. Why do you need the flexibility you think you need. An RDBMS is not meant to store the description of calculations in a varchar column in order to use the value of this column to create others table with te calculation as the defintition for a computed column. Could you tell us what it is you try to achieve on a conceptual level? Maybe we are able to point you to another type of solution.


    Thursday, April 25, 2013 6:29 PM