Handling NULL for DECIMAL fields
Posted: Sat Jun 21, 2008 9:34 am
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 .
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 .