IsNull returns Null if value is Null

[#1]

Hi

I'm getting strange behaviour from the IsNull() function. If there is a value it gives me 0. But if it's Null, the function returns Null and not -1.

If I do a Len() on the value, I also get Null where there is no value, so there really is no value.

I get this behaviour on both 64bit and 32bit.

Any help?

Thanks,

Brian

[kaushiknsolanki]

Hi,

   Can you upload the qvw file.

Regards,

Kaushik Solanki

[#3]

Further discovery -

I'm using the IsNull() to apply a rule to the data and join the result of the function to the same table that has the field value that I'm checking.

If I try to join the field to the table as follows, I get the failure I described:

Left Join (TABLE_A)

Load

     KEY_FIELD,

     IsNull(VALUE_FIELD) as NullCheck

Resident (TABLE_A)

But, if I make it a whole new table, it works as I expect it to:

NEW_TABLE:

Load

KEY_FIELD,

IsNull(VALUE_FIELD) as NullCheck

Resident (TABLE_A)

What small peculiarity am I missing?

Tks,

Brian

PS: Thanks, Kaushik, I will upload a version when I get a chance.

[erichshiino]

This is strange...

What happens if you right this:

Left Join (TABLE_A)

Load

     KEY_FIELD,

     if( IsNull(VALUE_FIELD),0,1)  as NullCheck

Resident (TABLE_A)

?

or

Left Join (TABLE_A)

Load

     KEY_FIELD,

     if( len(VALUE_FIELD),1,0)  as NullCheck

Resident (TABLE_A)