Passes through Lookup, can't handle Null in Transformer...

[Daddy Doma]

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?

[Kryt0n]

This might help...

Instead of the options you have been trying, why not use "Matches" instead? i.e. If field Matches "4N" : CHAR(253) : "" then it is OK else error.

(If the field is 4 numeric characters or empty then OK else error)

[Daddy Doma]

No good. The Matches command is valid in DataStage Server Edition only - we are using Enterprise Edition 7.5.1.a.

[Kryt0n]

That's a bit of a pain... (although you mean a server job command). We use 7.5.1, it works for server jobs but as you note, not for parallel jobs... although the logical reason for that is beyond me!

[lshort]

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.