How do I replace NULL with zero in an SSIS expression?

Question

I have two columns ActivityCount and ParentValue. I created an expression:

ROUND((ActivityCount / ParentValue) / 100,16) * 100

But the problem is it returns NULL for some columns and I wanted to replace NULL with 0. I can't seem to find the answers.

user756519 · Accepted Answer · 2013-02-21 15:13:40Z

8

Expression:

ISNULL(ParentValue) || (ParentValue == 0) ? 0 : ROUND(ISNULL(ActivityCount) ? 0 : ActivityCount / ParentValue / 100,16) * 100

For readability:

ISNULL(ParentValue) || (ParentValue == 0) 
    ? 0 
    : ROUND((ISNULL(ActivityCount) 
                ? 0 
                : ActivityCount / ParentValue) / 100
             , 16) * 100

What it does:

If ParentValue field is NULL or has Zero, you do not have to perform a calculation because you will encounter Divide by Zero error. So, simply exit the expression by returning 0.
If ActivityCount field is NULL, replace it with zero before performing the calculation.

Recommendation:

I would recommend using COALESCE on the source query to replace the NULL values with zeroes and the calculation, if possible.

answered Feb 21, 2013 at 15:13

user756519

Sign up to request clarification or add additional context in comments.

2 Comments

William Salzman Over a year ago

Again a top quality answer @Siva +1

Djbril Over a year ago

Thank You Siva that was awesome and Thank you to everybody else much appreciated!!!

gbn · Accepted Answer · 2013-02-21 14:53:00Z

2

Test with ISNULL (SSIS, not the SQL expression), use the conditional operator

ISNULL(ROUND((ActivityCount / ParentValue) / 100,16) * 100) ? 0 : ROUND((ActivityCount / ParentValue) / 100,16) * 100

answered Feb 21, 2013 at 14:53

gbn

434k84 gold badges602 silver badges690 bronze badges

Comments

Pandian · Accepted Answer · 2013-02-21 14:55:59Z

2

Try like below... it will help you...

ISNULL(ROUND((ActivityCount / ParentValue) / 100,16) * 100) || (ROUND((ActivityCount / ParentValue) / 100,16) * 100)= "" ? 0 : ROUND((ActivityCount / ParentValue) / 100,16

answered Feb 21, 2013 at 14:55

Pandian

9,1562 gold badges26 silver badges34 bronze badges

Comments

William Salzman · Accepted Answer · 2013-02-21 15:13:02Z

2

I would have said:

ISNULL(ActivityCount) || ISNULL(ParentValue) ? 0 : ROUND((ActivityCount/ParentValue)/100,16)*100

That way you are testing the simplest cases that will cause a NULL result rather than calculating the end case multiple times in your conditional.

(That being said, any of these will technically work.)

answered Feb 21, 2013 at 15:13

William Salzman

6,4463 gold badges35 silver badges51 bronze badges

Collectives™ on Stack Overflow

How do I replace NULL with zero in an SSIS expression?

4 Answers 4

Expression:

For readability:

What it does:

Recommendation:

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Expression:

For readability:

What it does:

Recommendation:

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related