Modify Stage Null Handling issue

[Havoc]

Hi

I have a job design that looks like :

Code: Select all

            SeqFile
                |
                |
DB2 -> Lookup(continue mode) -> Modify Stage -> SeqFile2

The Lookup stage is in continue mode. The Modify stage has two specficiations :

1) Keep Col1,Col2,Col3
2) Col2:string = handle_null (Col2,"?")

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 ?

Thanks in advane

[balajisr]

Modify stage is not required.

Remove modify stage and connect SeqFile2 to lookup stage.Set null field value of the SeqFile2 to '?'.

[Havoc]

Modify stage is not required.

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 :

Code: Select all

 


            SeqFile
                |
                |                   
DB2 -> Lookup(continue mode) -> Modify Stage -> Merge

[ray.wurlod]

Merge stage requires at least two inputs and the same number of outputs. Is this not your entire job design?

[Havoc]

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.

[ray.wurlod]

What function are you using?

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 Operators manual correctly identifies the function as handle_null() for the Modify stage.

[Havoc]

What function are you using?

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.

[sanjay]

Hi

check if it is empty string

sanjay

What function are you using?

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.

[Havoc]

Hi

check if it is empty string

sanjay

What function are you using?

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 ?

[sanjay]

Try setting ? in properties of sequential file stage for that column

Thanks
Sanjay

Hi

check if it is empty string

sanjay

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 ?

[balajisr]

Try using peek stage instead of sequential file.

[ray.wurlod]

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).

[panchusrao2656]

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.