Handling NULL for DECIMAL fields

[199542]

Hi Everybody ,
I am working on 8.0.1 version of datastage ( Information server ) .
My job design is as follows .

Seq file -------->> transformer ----------> oracle enterprise stage .

My sequential file contains decimal fields .I am specifying the format as packed ,No overpunch because it came from mainframe.With these options i am able to view the data properly .

My job is working properly till i get NULL values in the input sequential file .As sequential files wont have NULL ,its coming as string with spaces.My file is a FIXED WIDTH .
When i am getting NULL ( String with spaces ) in a field of decimal datatype that particular record is getting rejected .
So ,In order to avoid it i am using NULL handling as below .

If field F1 DECIMAL(5,0) then i am specifying NULLFIELD VALUE =' '
With these above settings i am able to view data with NULL in that particular field.But this particular record is causing oracle error saying precision specified too large.

In my transformer my transformation is as follows
If IsNull( F1 ) then SetNull() ELSE F1 ------>> throwing oracle error

If IsNull(F1) then 1 else F1 ------> Job running Sucessfully .But instead of 1 i want to insert NULL.

1) So ,I want to know if this SetNull() is causing error ??

2) Is there any way to insert NULL into DECIMAL fields through datastage ??

Hope i am clear .Request you all to help in this regard.

Thank you all in advance .

[chulett]

In my transformer my transformation is as follows
If IsNull( F1 ) then SetNull() ELSE F1 ------>> throwing oracle error

Any error in particular or do we get to guess?

[199542]

Hi thank you chulett for your prompt reply.

Sorry for not mentioning the error correctly .

Below is the error i am facing when i am mentioning

If IsNull( F1 ) then SetNull() ELSE F1 in the transformer ....

ORA-01438: value larger than specified precision allowed for this column.

If Isnull(F1) then 1 else F1 ---- is working fine .

But i want to insert NULL . Could you please help me ??

Thank you all.

[chulett]

How is that field defined in your Oracle table - NUMBER(what,what) ?

[199542]

In Sequential file ,I mentioned it as F1 --- DECIMAL(5,0).

In Oracle Enterprise Stage I specified it as DECIMAL(5,0).

While creating table I specified it as F1 number(5,0).


Thank you .

[ray.wurlod]

The error message suggests that there is a value in your data that has more than five significant digits.

[199542]

Thank you ray for your response .

My input sequential file is not having any records with field f1 greater than 5.I am having fixed width flat file .I specified in metadata of sequential

Even I tried the following

If IsNull(F1) then 99999 else F1 --------- Inserting 99999 in database for NUll value So its working as expected .

If IsNull(F1) then SetNull() else F1 ----- its giving oracle precision error .I am unable to comprehend why this error is coming even though that field data is lessthan or equal to the field length.

I saw in one of the posts that there was a patch for inserting NULL in oracle database through oracle enterprise stage in 7.5.x2.Anybody have anyidea about this patch in 8.0.1 version.

Thank you all.

[sreddy]

Hi


IF ISNULL(TRIM(NAME)) THEN 99999
ELSE NAME

You specified oracle table side also Decimal (5, 0).
are you check in the physical definition of the oracle table, if not please check it once.

If you have problem with table definition discuss with oracle admin
Decimal (precision, Scale)

datastage recommends Modify stage ( Null , Not Null ) Values.

CHulett and ray are definitely come up with solution.

This is my idea only...

[199542]

Thank You reddy for your response .I saw the physical table definition .Its NUMBER(5,0) only .Still i am unable to resolve this problem .Have anybody experienced this type of problem ?? Please suggest .

Thank you all

[AmeyJoshi14]

Hi,
We were also facing the same issue a long time back...
So instead of checking IsNull use IsNotNull....that is

If IsNotNull( F1 ) then F1 ELSE SetNull()

I do not know what is the reason... but after modifing the if else logic it is working fine... I know it is strange...but it worked...

I am not sure about this..but try this one as well:
If F1=0 THEN SetNull() ELSE F1