Using NULL values in transformer constraints

[travissolt]

Is it possible to exclude or include data within a transformer constraint based on if the field is NULL. Example below

isnull(FIELD1) or FIELD1>FIELD2 or FIELD1=FIELD2



in the transformer logic itself (not the constraint area) I can get around the nulls by saying something like if isnull(FIELD1) then setnull() else TRIM(FIELD1) i am hoping the transformer constraints do not have the same issue with NULL handling. Any FIELD1 that comes in as NULL from the lookup is thrown out because of the constraint. If I remove the constraint then the rows will be passed through.

Thanks

[ArndW]

As long as you check for null first and just use OR clauses you can do this in a constraint. As soon as you have an AND then all conditions are evaluated and any normal operation on a NULL field will cause an error.

[travissolt]

I tried and tried and I could not get it to use a field with a NULL possibility in the transformer constraint. As a work around I used a modify stage prior to the transformer and converted the NULL's to a usable value which I then used in the constraint.



OLD constraint logic
isnull(FIELD1) or FIELD1>FIELD2 or FIELD1=FIELD2

Modify stage setting FIELD1 to '0' where it was null

NEW constraint logic

FIELD1='0' or FIELD1>FIELD2 or FIELD1=FIELD2