Passes through Lookup, can't handle Null in Transformer...
Posted: Mon Jul 11, 2005 10:53 pm
Source A has 3 distinct records.
Source B has two distinct records. Each is a match to data in Source A.
I have a lookup between the two sources. All records continue to the next transformer stage.
In the transformer, I want to evaluate the fourth character of one of the columns for each record. This column is a char(4).
Valid data is a numeric with leading zeroes, e.g. '0099'.
Corrupt data will have alpha or other characters, e.g. '009{'.
In two seperate stage variables, I use the following functions:
(FourthChar)
Right(stream.field,1)
For the examples above, this returns a '1' and '{' respectively.
(BusRule)
IF IsValid("Int8",FourthChar) THEN '' ELSE Error Message
The first example passes with no Error Message returned, because '1' is a valid Int8.
The second example raises an Error Message, because '{' is not a valid Int8.
BUT...
There were three records in Source A, and one of these has come through the lookup into the transformer with no match in the offending column.
The output from the lookup is nullable. The record with no match is triggering the Error Message in the BusRule stage variable.
I have tried, in FourthChar, to set the null column as a valid Int8 to avoid triggering the BusRule:
Right(NullToZero(stream.field),1)
Right(NulltoValue(stream.field,'0000'),1)
Right(NulltoValue(stream.field,'0009'),1)
Right(Trim(stream.field),1)
If IsNull(stream.field) Then '0' Else Right(stream.field,1)
If Len(trim(stream.field)) = 0 Then '1' Else Right(stream.field,1)
Nothing works.
When an error message is triggered, the offending stream.field is output to a sequential text file.
When viewed in a variety of text editors, the value is represented as either ' ' or four squares ('[][][][]').
Thoughts?
Source B has two distinct records. Each is a match to data in Source A.
I have a lookup between the two sources. All records continue to the next transformer stage.
In the transformer, I want to evaluate the fourth character of one of the columns for each record. This column is a char(4).
Valid data is a numeric with leading zeroes, e.g. '0099'.
Corrupt data will have alpha or other characters, e.g. '009{'.
In two seperate stage variables, I use the following functions:
(FourthChar)
Right(stream.field,1)
For the examples above, this returns a '1' and '{' respectively.
(BusRule)
IF IsValid("Int8",FourthChar) THEN '' ELSE Error Message
The first example passes with no Error Message returned, because '1' is a valid Int8.
The second example raises an Error Message, because '{' is not a valid Int8.
BUT...
There were three records in Source A, and one of these has come through the lookup into the transformer with no match in the offending column.
The output from the lookup is nullable. The record with no match is triggering the Error Message in the BusRule stage variable.
I have tried, in FourthChar, to set the null column as a valid Int8 to avoid triggering the BusRule:
Right(NullToZero(stream.field),1)
Right(NulltoValue(stream.field,'0000'),1)
Right(NulltoValue(stream.field,'0009'),1)
Right(Trim(stream.field),1)
If IsNull(stream.field) Then '0' Else Right(stream.field,1)
If Len(trim(stream.field)) = 0 Then '1' Else Right(stream.field,1)
Nothing works.
When an error message is triggered, the offending stream.field is output to a sequential text file.
When viewed in a variety of text editors, the value is represented as either ' ' or four squares ('[][][][]').
Thoughts?