Options for handling a NULL field

[vmcburney]

Within a job that reads from a sequential file and writes to a Data Set I want to do a simple IsNull derivation - if FieldA is NULL then output FieldB, else output FieldA. Currently I am doing this in a transformer using simple BASIC code. I want to know whether it can be done in another type of stage. I have heard transformer stage doesn't perform as well as most of the other parallel stage types.

I tried doing it in a Modify stage with the specification FIELDA:string = NullToValue (FIELDA, FIELDB) but I got the following error:

main_program: Error parsing modify adapter: Error in binding: Unknown conversion: NullToValue
Expected destination field selector, got: ")"; input:
FIELDA:string = NullToValue(FIELDA, FIELDB)

[richdhan]

Hi Vincent,

A basic search would have given you the results. The modify stage uses orchestrate functions.

In the Specification use the following

output_column=handle_null(input_column,"value for the column")

HTH
--Rich

Pleasure in job brings perfection in work-Aristotle

[sanjay]

Hi
Syntax is
In specification
FIELDA = handle_null(FIELDA,FIELDB)

Sanjay

Within a job that reads from a sequential file and writes to a Data Set I want to do a simple IsNull derivation - if FieldA is NULL then output FieldB, else output FieldA. Currently I am doing this in a transformer using simple BASIC code. I want to know whether it can be done in another type of stage. I have heard transformer stage doesn't perform as well as most of the other parallel stage types.

I tried doing it in a Modify stage with the specification FIELDA:string = NullToValue (FIELDA, FIELDB) but I got the following error:

main_program: Error parsing modify adapter: Error in binding: Unknown conversion: NullToValue
Expected destination field selector, got: ")"; input:
FIELDA:string = NullToValue(FIELDA, FIELDB)

[vmcburney]

Thanks guys, the handle_null worked. This function does not appear anywhere in the Parallel Job Developers Guide, nor does it appear in the Developer's Help file, where are these functions documented?

According to the Modify stage help file "A full listing of the conversion functions available is given in Parallel Job Developer's Guide." This doesn't appear to be the case.

[richdhan]

Hi Vincent,

The documentation on these are available in Orchestrate Operator's Reference.

--Rich

When someone does something good, Applaud.
You will make two people happy.

[luie]

I tried using Handle_Null as above but it doesn't work.

TRANSACTION_DATE=Handle_Null(TRANSACTION_DATE,CREATED_TIMESTAMP)

TRANSACTION_DATE AND CREATED_TIMESTAMP are TIMESTAMP columns

The job fails with the following error:

check_nulls: Error when checking operator: When binding output schema variable "outRec": When binding output interface field "TRANSACTION_DATE" to field "TRANSACTION_DATE": Bad literal for type timestamp: CREATED_TIMESTAMP [api/interface_rep.C:1103]

I also tried creating a new output column but same error.

In another post, I read that Modify stage accepts only one argument, the others are supposed to be constant. Does that apply to Handle_Null? I tried replacing the second argument with a literal and it worked. How come it works for others as they claim above? I'm using version 7.1

Thanks

[DSguru2B]

Try handle_null().