DSXchange

Hi I am getting this below error for a job.The columns present in the job are nullable only.

If I am doning the null handling in the below way then the below error its giving.

If (IsNull(column1) or TrimF(TrimB(column1))='') Then '****'
Else TrimF(TrimB(Convert('"','',column1)))

APT_CombinedOperatorController,1: Null handling function called on a not nullable field.
Null handling function called on a not nullable field.
Null handling function called on a not nullable field.
Null handling function called on a not nullable field.

APT_CombinedOperatorController,1: Un-handled conversion error on field "BIRTH_CNTRY " from source type "string[max=255]" to destination type "int8":
source value=""; the result is non-nullable and there is no handle_null to specify a default value.
Un-handled conversion error on field "PASSPORT " from source type "string[max=255]" to destination type "int8":
source value=""; the result is non-nullable and there is no handle_null to specify a default value.
Un-handled conversion error on field "NATIONAL_ID " from source type "string[max=255]" to destination type "int8":
source value=""; the result is non-nullable and there is no handle_null to specify a default value.
Un-handled conversion error on field "DRIVING_LICENSE " from source type "string[max=255]" to destination type "int8":
source value=""; the result is non-nullable and there is no handle_null to specify a default value.

JoinSerNo,1: sendWriteSignal() failed on node SAW1234 ds = 16 conspart = 1 Broken pipe

JoinSerNo,1: Write to dataset on [fd 12] failed (Error 0) on node node2, hostname SAW1234

node_node2: APT_PMCleanUp::deleteFileRegistration: file /M=/Scratch/SAW1234node2.0000000000000000 is not registered.

Are you using a stage variable for these derivations ? use NullToValue or NullToEmpty in case of string fields or NullToZero for numeric fields in your stage var derivations - it can help.

Seems to me that column1 is defined as not nullable and you've used an IsNull() function on it. DataStage has objected to this.

Actually In production the job is getting aborted due to not doing the Null handling.
But the same job is working fine is Dev and QA server(Without any Null handling).

Trim( Convert('"','', ToXfmTrm.col1) )

There are four columns for which this error is coming. These columns I am taking from four reference tables based on a key value from the source table as left outer with the main table. After that I am doing the null handling for the four fields as there may be Null records may come due to left join.In job level all the columns are Nullable.but
I have tried different ways to do the null handling. But nothing seems to be working.

I tried with enabling the environment valiables APT_MONITOR_SIZE=50000 ,APT_MONITOR_INTERVAL=5 and APT_NO_JOBMON=True.

Step 1:
If Trim(NulltoEmpty(ToXfmTrm.col1))='' Then '****'
Else Trim( Convert('"','', ToXfmTrm.col1) )

Step 2:
If (IsNull(ToXfmTrm.col1) Or Trim(ToXfmTrm.col1)='') Then Null'****'
Else Trim( Convert('"','', ToXfmTrm.col1) )

Is it due mismatch of patches this error I am getting.


Error Message:
===============
XfmTrm: When checking operator: When binding input interface field "input0Int8BIRTH_CNTRY_0" to field "BIRTH_CNTRY": Using "notnull" conversion with non-nullable input.
XfmTrm: When checking operator: When binding input interface field "input0Int8PASSPORT_1" to field "PASSPORT": Using "notnull" conversion with non-nullable input.
XfmTrm: When checking operator: When binding input interface field "input0Int8NATIONAL_ID_2" to field "NATIONAL_ID": Using "notnull" conversion with non-nullable input.
XfmTrm: When checking operator: When binding input interface field "input0Int8DRIVING_LICENSE_3" to field "DRIVING_LICENSE": Using "notnull" conversion with non-nullable input.

APT_CombinedOperatorController,0: Null handling function called on a not nullable field.
Null handling function called on a not nullable field.
Null handling function called on a not nullable field.
Null handling function called on a not nullable field.

APT_CombinedOperatorController,0: Un-handled conversion error on field "BIRTH_CNTRY " from source type "string[max=255]" to destination type "int8":

source value="AW"; the result is non-nullable and there is no handle_null to specify a default value.
APT_CombinedOperatorController,1: Null handling function called on a not nullable field.
Null handling function called on a not nullable field.

APT_CombinedOperatorController,0: Un-handled conversion error on field "PASSPORT " from source type "string[max=255]" to destination type "int8":

source value=""; the result is non-nullable and there is no handle_null to specify a default value.
Un-handled conversion error on field "NATIONAL_ID " from source type "string[max=255]" to destination type "int8":
source value="ID. 61.06.30.43"; the result is non-nullable and there is no handle_null to specify a default value.
Un-handled conversion error on field "DRIVING_LICENSE " from source type "string[max=255]" to destination type "int8":
source value=""; the result is non-nullable and there is no handle_null to specify a default value.

APT_CombinedOperatorController,1: Null handling function called on a not nullable field.
Null handling function called on a not nullable field.

APT_CombinedOperatorController,1: Un-handled conversion error on field "BIRTH_CNTRY " from source type "string[max=255]" to destination type "int8":
source value="AW"; the result is non-nullable and there is no handle_null to specify a default value.
Un-handled conversion error on field "PASSPORT " from source type "string[max=255]" to destination type "int8":
source value=""; the result is non-nullable and there is no handle_null to specify a default value.
Un-handled conversion error on field "NATIONAL_ID " from source type "string[max=255]" to destination type "int8":
source value=""; the result is non-nullable and there is no handle_null to specify a default value.
Un-handled conversion error on field "DRIVING_LICENSE " from source type "string[max=255]" to destination type "int8":
source value=""; the result is non-nullable and there is no handle_null to specify a default value.

buffer(1),0: Fatal Error: APT_BufferOperator::writeAllData() write failed. This is probably due to a downstream operator failure.

buffer(2),0: sendWriteSignal() failed on node SW1234 ds = 15 conspart = 0 Broken pipe
buffer(2),0: Error in writeNoBlock - could not write 2002002

partha.natta wrote:..If (IsNull(column1) or TrimF(TrimB(column1))='') Then '****' Else TrimF(TrimB(Convert('"','',column1)))...

An "OR" condition will evaluate all elements, thus if column1 were to be nullable the statement above would not work.

But since "column1" is a stage variable it cannot contain a null value and any null handling here is unnecessary.

What is your derivation for "column1"? Is the "Trim( Convert('"','', ToXfmTrm.col1) ) "? If so, it should read "Trim( Convert('"','', NullToEmpty(ToXfmTrm.col1)) ) "