Adding Multiple Constraints to the target transformer

[ririr]

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

[ray.wurlod]

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) )

[vmcburney]

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.

[ririr]

Guys -

I applied another boolean but still doesn't work

( (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) )

Thanks for everyones input