Now Col2 (the non key column from Sequential file) should come as null from the lookup since it does not match with the Col1 key from the primary Col2 is a varchar(2) field. I wish to make all the null value Col2 fields into "?".
But all the null values still remain the same in the second sequential file a well. Is there something wrong with the specification mentioned in the Modify stage ?
Remove modify stage and connect SeqFile2 to lookup stage.Set null field value of the SeqFile2 to '?'.
Thanks for replying balaji ... My bad .. its actually not a SeqFile. I was just using it to see if the null value is being substituted. In actuality i have a merge stage there ... i used the sequential file to check if the value was being substituted but its not .. Any reasons why this could be happening ? Job design actually looks like :
ray.wurlod wrote:Merge stage requires at least two inputs and the same number of outputs. Is this not your entire job design? ...
Yes its not my entire job design, Lets just consider , the rest of my job is after the modify stage. The problem is that when Lookup is in 'Continue' and unmatched rows are output, the null values are not handled in the modify stage.
Is there any solution to this ? I really want to avoid using a Transformer.
There is an error in the Parallel Job Developer's Guide where it gives the Transformer stage function NullToValue() for the Modify stage.
The Orchestrate ...
I am using the handle_null function. The specification mentioned in the modify stage is :
Col2:string = handle_null (Col2,"?")
I did a small experiment, nulls coming directly from a table are handled perfectly fine. But nulls resulting from unmatched rows in a Lookup come out blank.
There is an error in the Parallel Job Developer's Guide where it gives the Transformer stage function NullToValue() for the Modify stage.
The Orchestrate ...
I am using the handle_null function. The specification mentioned in the modify stage is :
Col2:string = handle_null (Col2,"?")
I did a small experiment, nulls coming directly from a table are handled perfectly fine. But nulls resulting from unmatched rows in a Lookup come out blank.
There is an error in the Parallel Job Developer's Guide where it gives the Transformer stage function NullToValue() for the Modify stage.
The Orchestrate ...
I am using the handle_null function. The specification mentioned in the modify stage is :
Col2:string = handle_null (Col2,"?")
I did a small experiment, nulls coming directly from a table are handled perfectly fine. But nulls resulting from unmatched rows in a Lookup come out blank.
Yeah when writing to a sequential file. It is an empty string. So is there anyway to use the modify stage in this case? Is there another way to replace that empty string with a '?' other than using the transformer ?
Try setting ? in properties of sequential file stage for that column
Thanks
Sanjay
Havoc wrote:
sanjay wrote:Hi
check if it is empty string
sanjay
Havoc wrote:
I am using the handle_null function. The specification mentioned in the modify stage is :
Col2:string = handle_null (Col2,"?")
I did a small experiment, nulls coming directly from a table are handled perfectly fine. But nulls resulting from unmatched rows in a Lookup come out blank.
Yeah when writing to a sequential file. It is an empty string. So is there anyway to use the modify stage in this case? Is there another way to replace that empty string with a '?' other than using the transformer ?
Can you be certain theat the Col2 value coming into the Modify stage is NULL? Insert a Peek stage into this link to see what's actually happening (or compile in debug/trace mode).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Can you check whether the metadata of the input/output col from lookup is NULLABLE OR NOT NULLABLE. When it is NOT NULLABLE, then it returns a default value( in case of Varchar it is '' empty, decimal it returns zero, date it returns a default date). If it is NULLABLE then only it returns NULL.