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?
Passes through Lookup, can't handle Null in Transformer...
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 62
- Joined: Tue Jun 14, 2005 7:17 pm
- Location: Australia
- Contact:
-
- Premium Member
- Posts: 62
- Joined: Tue Jun 14, 2005 7:17 pm
- Location: Australia
- Contact:
if i am understanding your correctly a row that failed the lookup is triggering your error in the transformer. Have you tried reorganizing your if statement and adding a Null Test.
if Not(IsValid("Int8", FourthChar)) then
if IsNull(FourthChar) then '' Else
Error
end else
''
end
My syntax my be off but conceptually this could work.
if Not(IsValid("Int8", FourthChar)) then
if IsNull(FourthChar) then '' Else
Error
end else
''
end
My syntax my be off but conceptually this could work.
Lance Short
"infinite diversity in infinite combinations"
***
"The absence of evidence is not evidence of absence."
"infinite diversity in infinite combinations"
***
"The absence of evidence is not evidence of absence."