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
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- 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