CODEWITHSUNDEEP
ssis
Djbril
Djbril

Reputation: 895

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

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.

Upvotes: 4

Views: 18762

Answers (4)

user756519
user756519

Reputation:

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.

Upvotes: 8

William Salzman
William Salzman

Reputation: 6446

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

Upvotes: 2

Pandian
Pandian

Reputation: 9136

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

Upvotes: 2

gbn
gbn

Reputation: 432662

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

Upvotes: 2

Related Questions