Hi,
We have 5 sequential files getting joined using Left outer join. Only the master record has the data and no child record is having data. But when the data is coming out from the JOIN stage is displaying 0 values into the decimal fileds that are coming from the child records. Where as our requirement is to display NULL value in the decimal field if there is no i/p data.
For this we tried using Nullable property for the respective decimal columns as YES. In this case the value is being displayed as NULL for the decimal fields those are having precision as > 0 but the job is being aborted with the following error message for the decimal fields those are not having any precision.
"Value larger than specified precision"
But the length of the field is defined properly through out all the stages including Oracle stage as per the defined structure of Oracle table.
Can anybody please help me out to solve this issue.
Thanks.
NULL value in DECIMAL fields
Moderators: chulett, rschirm, roy
Re: NULL value in DECIMAL fields
Generally this type of warnings gives when incoming field length is larger than the outgoing field length.
some where small missmatch occured between incoming field length and out going field lengh.
Check once more.
Regards,
Venkat.
some where small missmatch occured between incoming field length and out going field lengh.
Check once more.
Regards,
Venkat.
venki
-
- Participant
- Posts: 53
- Joined: Fri Mar 07, 2008 1:17 am
Re: NULL value in DECIMAL fields
verify wrote:Hi,
We have 5 sequential files getting joined using Left outer join. Only the master record has the data and no child record is having data. But when the data is coming out from the JOIN stage is displaying 0 values into the decimal fileds that are coming from the child records. Where as our requirement is to display NULL value in the decimal field if there is no i/p data.
For this we tried using Nullable property for the respective decimal columns as YES. In this case the value is being displayed as NULL for the decimal fields those are having precision as > 0 but the job is being aborted with the following error message for the decimal fields those are not having any precision.
"Value larger than specified precision"
But the length of the field is defined properly through out all the stages including Oracle stage as per the defined structure of Oracle table.
Can anybody please help me out to solve this issue.
Thanks.
hi there,
can u please let us know the nullability of the target columns you are loading the decimal fields to?
ETL DEVELOPER
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hi,
The target database field is NULLABLE only. Also the length of input field and output fields are same.
What should we do if the join stage is giving NULL for the decimal columns and when the same value is being loaded into the Oracle it is populating with 0 where as when I use target as sequential file instead Oracle stage it is populating as NULL.
Is this a bug in the 8.0.1 version?
Thanks
The target database field is NULLABLE only. Also the length of input field and output fields are same.
What should we do if the join stage is giving NULL for the decimal columns and when the same value is being loaded into the Oracle it is populating with 0 where as when I use target as sequential file instead Oracle stage it is populating as NULL.
Is this a bug in the 8.0.1 version?
Thanks
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Data types are not enforced in sequential files. Therefore, although you might have a field defined as Decimal[5,0], there's nothing to prevent it containing eight digits. You still need to check your data - create a job to read the file and to report the length of the field that is generating the error message.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.