Here is the situation:
There following four columns are being read from the source side. I have created a constraint on the target transformer to only write the records if any of the column is not null
(NOT(IsNull(DSLink3.NEWCOLA)) OR NOT(IsNull(DSLink3.OLDCOLA)) OR NOT(IsNull(DSLink3.NEWCOLB)) OR NOT(IsNull(DSLink3.OLDCOLB)))
This works fine..
Also, I want to add another constraint to the same transformer to check, if both NEW AND OLD COLUMNS are not nulls and also verify if the data values match between OLD AND NEW COLUMNS. If the data value matches then do not write the data row to the target table.
Currently, I have something like this but wont work..
(DSLink3.NEWCOLA<>DSLink3.OLDCOLA) OR (DSLink3.NEWCOLB<>DSLink3.OLDCOLB)
Any help is appreciated..
Thanks
Adding Multiple Constraints to the target transformer
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
How about using one more Boolean operator:
Code: Select all
( (NOT(IsNull(DSLink3.NEWCOLA)) OR NOT(IsNull(DSLink3.OLDCOLA)) OR NOT(IsNull(DSLink3.NEWCOLB)) OR NOT(IsNull(DSLink3.OLDCOLB))) ) AND ( DSLink3.NEWCOLA<>DSLink3.OLDCOLA) OR (DSLink3.NEWCOLB<>DSLink3.OLDCOLB) )
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
I would think that the null check on the OLD columns is unnecessary, if any OLD column is NULL then surely you would want to update it with a NEW column.
You have two constraint business rules here, 1) New and Old columns cannot be null. 2) New columns cannot have the same value as Old columns. Consider splitting the code for these into two stage variable that are flags with names like NULLFOUND and DATACHANGED. You will find this is more maintainable then a very long constraint with lots of nots and brackets. Your constraint then becomes "NOT(NULLFOUND) AND DATACHANGED".
If you add a reject link to this to capture all rows with NULLS in them, in case someone wants to know how many rows are failing the first business rule, you can reuse the flag on the reject constraint. "NOT(NULLFOUND)".
You will notice now that someone opening this transformer can see straight away what the constraint means.
You have two constraint business rules here, 1) New and Old columns cannot be null. 2) New columns cannot have the same value as Old columns. Consider splitting the code for these into two stage variable that are flags with names like NULLFOUND and DATACHANGED. You will find this is more maintainable then a very long constraint with lots of nots and brackets. Your constraint then becomes "NOT(NULLFOUND) AND DATACHANGED".
If you add a reject link to this to capture all rows with NULLS in them, in case someone wants to know how many rows are failing the first business rule, you can reuse the flag on the reject constraint. "NOT(NULLFOUND)".
You will notice now that someone opening this transformer can see straight away what the constraint means.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn