Transform not recognizing change to one particular field
Posted: Wed Jul 14, 2004 6:50 am
We're having a really tricky problem. Our daily processes updates Table B to stay identical to Table A.
One of our server jobs compares the current contents of Table A to the existing contents of Table B.
In this job, Table A's contents come in to a transform as a sequential file, and Table B's contents are in a hash file fed from an ODBC stage - this is the reference link.
In the transform, the keys are matched on the input link and reference link. The one output is the file with all the records that changed.
The constraint that selects records is as follows:
(NullToEmpty(existingRecs.field_1) <> NullToEmpty(lkupDBRecs.field_1)) OR
(NullToEmpty(existingRecs.field_2) <> NullToEmpty(lkupDBRecs.field_2)) OR
(NullToEmpty(existingRecs.test_code_2) <> NullToEmpty(lkupDBRecs.test_code_2)) OR
(NullToEmpty(existingRecs.field_3) <> NullToEmpty(lkupDBRecs.field_3)) OR
(NullToEmpty(existingRecs.test_code_3) <> NullToEmpty(lkupDBRecs.test_code_3)) OR
(NullToEmpty(existingRecs.field_4) <> NullToEmpty(lkupDBRecs.field_4)) OR
(NullToEmpty(existingRecs.field_5) <> NullToEmpty(lkupDBRecs.field_5)) OR
(NullToEmpty(existingRecs.field_6) <> NullToEmpty(lkupDBRecs.field_6)) OR
(NullToEmpty(existingRecs.field_7) <> NullToEmpty(lkupDBRecs.field_7)) OR
(NullToEmpty(existingRecs.field_8) <> NullToEmpty(lkupDBRecs.field_8)) OR
(NullToEmpty(existingRecs.first_name) <> NullToEmpty(lkupDBRecs.first_name)) OR
(NullToEmpty(existingRecs.last_name) <> NullToEmpty(lkupDBRecs.last_name)) OR
(NullToEmpty(existingRecs.email_address) <> NullToEmpty(lkupDBRecs.email_address)) OR
(NullToEmpty(existingRecs.phone_free_form_number) <> NullToEmpty(lkupDBRecs.phone_free_form_number)) OR
(NullToEmpty(existingRecs.field_9) <> NullToEmpty(lkupDBRecs.field_9)) OR
(NullToEmpty(Trim(existingRecs.test_code)) <> NullToEmpty(Trim(lkupDBRecs.test_code)))
You can see it compares 16 fields.
My problem is that for certain records a change in one of these fields (e.g. field_2) will fulfill the constraint, but a change to test_code does not.
To make matters worse, a change to test_code in some records fulfills the constraint, but in other records it doesn't!
As you can see, this is totally unpredictable and very frustrating. Is there some kind of overflow or limit or something that could cause this behavior?
One of our server jobs compares the current contents of Table A to the existing contents of Table B.
In this job, Table A's contents come in to a transform as a sequential file, and Table B's contents are in a hash file fed from an ODBC stage - this is the reference link.
In the transform, the keys are matched on the input link and reference link. The one output is the file with all the records that changed.
The constraint that selects records is as follows:
(NullToEmpty(existingRecs.field_1) <> NullToEmpty(lkupDBRecs.field_1)) OR
(NullToEmpty(existingRecs.field_2) <> NullToEmpty(lkupDBRecs.field_2)) OR
(NullToEmpty(existingRecs.test_code_2) <> NullToEmpty(lkupDBRecs.test_code_2)) OR
(NullToEmpty(existingRecs.field_3) <> NullToEmpty(lkupDBRecs.field_3)) OR
(NullToEmpty(existingRecs.test_code_3) <> NullToEmpty(lkupDBRecs.test_code_3)) OR
(NullToEmpty(existingRecs.field_4) <> NullToEmpty(lkupDBRecs.field_4)) OR
(NullToEmpty(existingRecs.field_5) <> NullToEmpty(lkupDBRecs.field_5)) OR
(NullToEmpty(existingRecs.field_6) <> NullToEmpty(lkupDBRecs.field_6)) OR
(NullToEmpty(existingRecs.field_7) <> NullToEmpty(lkupDBRecs.field_7)) OR
(NullToEmpty(existingRecs.field_8) <> NullToEmpty(lkupDBRecs.field_8)) OR
(NullToEmpty(existingRecs.first_name) <> NullToEmpty(lkupDBRecs.first_name)) OR
(NullToEmpty(existingRecs.last_name) <> NullToEmpty(lkupDBRecs.last_name)) OR
(NullToEmpty(existingRecs.email_address) <> NullToEmpty(lkupDBRecs.email_address)) OR
(NullToEmpty(existingRecs.phone_free_form_number) <> NullToEmpty(lkupDBRecs.phone_free_form_number)) OR
(NullToEmpty(existingRecs.field_9) <> NullToEmpty(lkupDBRecs.field_9)) OR
(NullToEmpty(Trim(existingRecs.test_code)) <> NullToEmpty(Trim(lkupDBRecs.test_code)))
You can see it compares 16 fields.
My problem is that for certain records a change in one of these fields (e.g. field_2) will fulfill the constraint, but a change to test_code does not.
To make matters worse, a change to test_code in some records fulfills the constraint, but in other records it doesn't!
As you can see, this is totally unpredictable and very frustrating. Is there some kind of overflow or limit or something that could cause this behavior?