# Expression Logic - How to get rid of NULL values in expression • ### Question

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

Example:  =  +  - (﻿﻿/ ( + ))

In this Column  value is depended on columns , , ,  and 

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

example NULL +  comes out to be NULL even if  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 - ﻿﻿

`( ( ( ( (  -  ) / ( + ) ) +  +  +  ) + ( (  -  ) /  ) ) ) * 100I 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

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

Thursday, April 25, 2013 7:31 AM

### All replies

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

Thursday, April 25, 2013 7:31 AM
• ( ( ( ( ( ISNULL(,0) - ISNULL(,0) ) / (ISNULL(,0) + ISNULL(,0)) ) + ISNULL(,0) + ISNULL(,0) + ISNULL(,0) ) + ( ( ISNULL(,0) - ISNULL(,0) ) / ISNULL(,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(,0) - ISNULL(,0) ) / (ISNULL(,0) + ISNULL(,0)) ) + ISNULL(,0) + ISNULL(,0) + ISNULL(,0) ) + ( ( ISNULL(,0) - ISNULL(,0) ) / ISNULL(,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
• 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
• 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
• but in this case all columns in denominator will be calculated as 0 and ultimately error...

example /.. if  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