IsNotNull function not working fine for me

[manuel.gomez]

I guess this must be something so simple to solve, but I cant find a solution. Searched the forum for similar posts, but none helped

I do have an input dataset file, connecting to a Lookup as input link. This lookup has one more reference input, another dataset.

I lookup by one field, and set lookup stage on 'Continue' mode for unmatched records. On the lookup output, I do have all the columns for the upstream, plus one of the columns of the reference input (that goes out with a different name), set to be nullable (although in the reference input was not).

After that, I entered a Transformer to detect records that matched, and those did not. The idea is to have two outputs in the transformer:one for rows that matched, the other one for unmatched.

Let me try to paint it for you

Dataset2
|
|
|
|
DS1-----> Lookup ------> Transformer


To me, was clear that constraints for both output links were:

Code: Select all

IsNull(look1.COD_GR_EMP_FIC)
IsNotNull(look1.COD_GR_EMP_FIC)

Absolutely all rows came down through the second link, so, no null values

After that, I changed the constraint to these functions that always worked for me in Server jobs

Code: Select all

len(trim(look1.COD_GR_EMP_FIC))=0
len(trim(look1.COD_GR_EMP_FIC))>0

Worked just perfect. But I feel this is kind of "slang", not proper.
Do you have any idea what all values are not null, even when it is proof than with the second method, there are rows coming with empty field, instead of null (unmatched records).

This turns to be the same when the column to check if there was a match is numeric. In these cases, value coming in the field is zero instead of null.

Thanks very much for your help

[manuel.gomez]

nobody can help?
thanks

[syeed]

Hi,

Please can you use diffrent Trim functions of datastage.

like TrimLeadingTrainling(), just try with this option.

Thanks,
Waseem

[ArndW]

Is "look1.COD_GR_EMP_FIC" a key column for the lookup? The result of a TRIM() function (of any type) on a NULL value is always null.

[manuel.gomez]

Is "look1.COD_GR_EMP_FIC" a key column for the lookup?

No

[ArndW]

And the column is declared as nullable and there is not default null value set for that column?

[manuel.gomez]

And the column is declared as nullable and there is not default null value set for that column?

Column is declared nullable in the lookup stage, but not in the upstream stage provinding the column (in this case, a dataset)

Default null value?? where do you set up that? I dont find any place to do it in the lookup stage

[ArndW]

Set it as nullable in the upstream stage, just to see. If you "edit row" on the column you can see and change numerous attributes, including how nulls are handled (if at all, in this case they shouldn't be handled). I assume that this link is the reference link in the lookup, not the column in the main data stream.

[manuel.gomez]

Set it as nullable in the upstream stage, just to see. If you "edit row" on the column you can see and change numerous attributes, including how nulls are handled (if at all, in this case they shouldn't be handled). I assume that this link is the reference link in the lookup, not the column in the main data stream.

I edit row but no option about null handling is giving. Actually, in the options field, I just see: None avaliable
Let me try what you suggest and I will give you result

[ray.wurlod]

Nullable property must be "Yes" before the Nullable options become available.

[manuel.gomez]

Nullable property must be "Yes" before the Nullable options become available.

I did it, but as said, no options avaliable