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
Using NULL values in transformer constraints
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 51
- Joined: Mon Feb 05, 2007 1:53 pm
-
- Participant
- Posts: 51
- Joined: Mon Feb 05, 2007 1:53 pm
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
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