How to Check Equality Between Two Column When Values Could Be Null

Question

How to Check Equality Between Two Column When Values Could Be Null

Josh Cash 25

In Azure Data Factory, I have two column values which I am trying assert if they are different so I know to update the source with a new value. The issue is that either value might be null as well and that breaks the comparison logic.

Value1 != Value2 or notEquals(Value1, Value2) both will return NULL instead of a true or false if one or both values is NULL. This is contradictorily of what I would expect.

I would expect:

Value1 = NULL, Value2 = 2.3 to result in true instead of NULL

Am I missing something or do I have to do null checks and then the comparison checks?

Accepted answer

2 additional answers

Your answer

Answer 1

Subashri Vasudevan 11,231

Hi,

Since both can be null and I didn't want to convert each number to some default value which could be a valid value, I did the following:

iif(isNull(Value1 != Value2), !isNull(Value1) || !isNull(Value2), Value1 != Value2)

While I am doing the equality check twice in some cases, it makes sure that I don't have to treat a valid value as a default and thus cause false negatives.

Thanks

Answer 2

Josh Cash 25

I ended up doing this:

Since both can be null and I didn't want to convert each number to some default value which could be a valid value, I did the following:

iif(isNull(Value1 != Value2), !isNull(Value1) || !isNull(Value2), Value1 != Value2)

While I am doing the equality check twice in some cases, it makes sure that I don't have to treat a valid value as a default and thus cause false negatives.

Subashri Vasudevan 11,231 Reputation points

2023-12-06T15:38:28.3766667+00:00

Alright, glad that you found out the right way of using the iif and isnull functions for your use case. Since original poster of question cannot accept his own answer, i have just reposted it. And request you to accept and close the thread.

Thanks.

Answer 3

Subashri Vasudevan 11,231

Hi,

You can use iif() function and isNull() function to achieve your requirement.( And I assume it is within a data flow).

iif(isNull(value1),toDecimal(0.00),toDecimal(value1))

And once you do this check and assign a default value to value1, you can compare value1 and value2 using equality operator.

Hope that resolves your issue.

Josh Cash 25 Reputation points

2023-12-06T14:47:41.1333333+00:00

the problem with this is that now 0.0 can't be a valid value.

If

Value1 = NULL and Value2 = 0.0 the comparison logic with your code would evaluate to False instead of the expected True

Share via

How to Check Equality Between Two Column When Values Could Be Null

2 additional answers

Your answer