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.
Null handling function called on a not nullable field
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 32
- Joined: Tue Mar 09, 2010 5:56 am
- Location: Bangalore
Null handling function called on a not nullable field
Thanks & Regards,
Partha
Partha
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 32
- Joined: Tue Mar 09, 2010 5:56 am
- Location: Bangalore
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
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
Thanks & Regards,
Partha
Partha
Re: Null handling function called on a not nullable field
An "OR" condition will evaluate all elements, thus if column1 were to be nullable the statement above would not work.partha.natta wrote:..If (IsNull(column1) or TrimF(TrimB(column1))='') Then '****' Else TrimF(TrimB(Convert('"','',column1)))...
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)) ) "