Handling Null values

[dprasanth]

Hi, I am trying to read a sequential file and updating a Oracle database using OCI stage.
The sequential file has null values in a field1 which is hardcoded as NULL. I am uploading this data to oracle which is a NUMBER field.

I tried to define the NULLABLE field as NULL in the sequential file stage. But still I am getting the following error
rnk_deqoci..Transformer_25: ORA-01722: invalid number

Can anyone of you please let me know how to handle such a situation. Sorry if this is too basic. You can guide me to the revelant documents and I will find it from ther. Thanks in advance

[king999]

u can try capturing the reject rows and then when you have all columns that have value null convert it to 0 using isnull and then try loading it there might be the better way but this is what is know

[ucf007]

add in the transformer something like:
if in.field01 = 'NULL' then @NULL

[king999]

hi
i have tried using

If DSLink4.Field002 = "null" then IsNull(DSLink4.Field002 ) else DSLink4.Field002

and it worked

[sun rays]

Hi, I am trying to read a sequential file and updating a Oracle database using OCI stage.
The sequential file has null values in a field1 which is hardcoded as NULL. I am uploading this data to oracle which is a NUMBER field.

I tried to define the NULLABLE field as NULL in the sequential file stage. But still I am getting the following error
rnk_deqoci..Transformer_25: ORA-01722: invalid number

Can anyone of you please let me know how to handle such a situation. Sorry if this is too basic. You can guide me to the revelant documents and I will find it from ther. Thanks in advance

"NULL" in your input stream is not the same as @null. Oracle is expecting a number field and your are giving it a "NULL" which is a string.
Define your column in the sequential file stage as varchar, and then in the transformer define the output column as integer. if the in.column ='Null' then default it to zero or @NULL.

[dprasanth]

hi
i have tried using

If DSLink4.Field002 = "null" then IsNull(DSLink4.Field002 ) else DSLink4.Field002

and it worked

Sorry for the late reply. I tried the above and it worked. As Sun Rays mentioned, the field002 in the sequential file was VARCHAR and it is hardcoded as NULL. But in the database the field was defined as decimal.So in the input file sequential stage I defined the variable as VARCHAR and did the above. But I am getting this oracle error.
RNK_SEQOCI_V1..Transformer_29: ORA-01861: literal does not match format string.Is it under the scope of this forum to answer this or should I go to my DBA?
Thanks a ton to everyone to tried to help me out in this query

[sun rays]

hi
i have tried using

If DSLink4.Field002 = "null" then IsNull(DSLink4.Field002 ) else DSLink4.Field002

and it worked

The IsNull() would return a boolean value. it does not convert the input field to null.

[sun rays]

hi
i have tried using

If DSLink4.Field002 = "null" then IsNull(DSLink4.Field002 ) else DSLink4.Field002

and it worked

it might have worked for you, because you might never had 'null' in input file, otherwise the same error mght have appeared.

[dprasanth]

hi
i have tried using

If DSLink4.Field002 = "null" then IsNull(DSLink4.Field002 ) else DSLink4.Field002

and it worked

it might have worked for you, because you might never had 'null' in input file, otherwise the same error mght have appeared.

Actually, I didn't have null in input field, but null was HARDCODED as NULL, may be that is the reason why it worked for me

[sun rays]

Try either of these depending on if your taget column is nullable or not.

If DSLink4.Field002 = "NULL" or IsNull(DSLink4.Field002 ) then @null else DSLink4.Field002.

If DSLink4.Field002 = "NULL" or IsNull(DSLink4.Field002 ) then 0 else DSLink4.Field002