Null Value handling in seqential file stages in DSEE 7.5.2
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
Null Value handling in seqential file stages in DSEE 7.5.2
Hello,
I am developing a job to join three sequential files using join stage to load output into one output sequential file. There are some nullable fields in all the three input files. While exporting the output data, the error comes
"When checking operator: When validating export schema: At field "XYZ": Exporting nullable field without null handling properties"...
To solve this problem, I defined :
Null field value=' ' (space) in the field default value under format tab of output sequential file stage. Now it gives the errors :
"When checking operator: When validating export schema: At field "XYZ": "null_field" length (1) must match field's fixed width (3)"
but I have many columns of length like 3,5,6 which are null at some point due to which I can't define a 'Null field value' as a fixed value.
I read that Pad char can be used in such cases but I dont know how to use that....
Please help..
Thanks in advance!
I am developing a job to join three sequential files using join stage to load output into one output sequential file. There are some nullable fields in all the three input files. While exporting the output data, the error comes
"When checking operator: When validating export schema: At field "XYZ": Exporting nullable field without null handling properties"...
To solve this problem, I defined :
Null field value=' ' (space) in the field default value under format tab of output sequential file stage. Now it gives the errors :
"When checking operator: When validating export schema: At field "XYZ": "null_field" length (1) must match field's fixed width (3)"
but I have many columns of length like 3,5,6 which are null at some point due to which I can't define a 'Null field value' as a fixed value.
I read that Pad char can be used in such cases but I dont know how to use that....
Please help..
Thanks in advance!
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Because you've specified fixed-width format, any null-substitute value you specify must have the correct number of characters as the field in question - in the cited case, three. So "_" (three space characters) would be a valid representation of NULL; a single space character is not.
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.
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
Thanks for response! you are right....
I did that but I have more than one colmun with null values. as an example, I have table definitions like this:
xyx char (3)
abc char (5)
klm char (9)
where xyz, abc and klm are different columns.
now if I specify three spaces for 'null field value' then it will be perfectly alright for column xyz because its length is 3. but not for column abc and klm and thats my problem. It will again give error with column abc and klm. I hope I was able to explain the probem correctly.
one of my question is " can we define null field value for each column separately?" if yes then how?
If we could do that. this problem will be solved.
I did that but I have more than one colmun with null values. as an example, I have table definitions like this:
xyx char (3)
abc char (5)
klm char (9)
where xyz, abc and klm are different columns.
now if I specify three spaces for 'null field value' then it will be perfectly alright for column xyz because its length is 3. but not for column abc and klm and thats my problem. It will again give error with column abc and klm. I hope I was able to explain the probem correctly.
one of my question is " can we define null field value for each column separately?" if yes then how?
If we could do that. this problem will be solved.
Attitude is everything....
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You need five character null-replacement for field abc and nine character null-replacement for field klm. As suggested, this is set on a per-column basis by accessing the extended properties via Edit Row menu option (or you can double-click on the row number in the Columns grid).
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.
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
I am now using Dataset stage as the destination. However, I cannot find the "null field value" when I go to edit row.balajisr wrote:In the columns tab of the sequential file, Right click on the column, select edit row and specify null field value for each columns.
Thanks in advance.
Attitude is everything....
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Data Set can happily store NULL, so you don't need to substitute anything else. This substitution technique is only relevant to sequential files and File Sets.
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.
Null handling for Decimal data.
Addressing the same issue on Null field handling.
I would like to make a small comment.
What if we have 1000 columns (very unlikely but...) and they are defined all chars...like Char (100) , char(200), char(500) so forth and so on.
In that case we are supposed to modify Null handling property for each and every column...
this can be handled by using varchar, so that we can can just define one null handling property e.g. Null Value=''
But, what is even more concerning is, when the data type is Decimal (15,4), Decimal (38,5) etc.
As decimal data type is fixed width, what it would mean is to give this type of property for each and every decimal type of column.
I wish, new versions come in with some kind of function or more generic property to handle many columns.
Regards,
Kash.
I would like to make a small comment.
What if we have 1000 columns (very unlikely but...) and they are defined all chars...like Char (100) , char(200), char(500) so forth and so on.
In that case we are supposed to modify Null handling property for each and every column...
this can be handled by using varchar, so that we can can just define one null handling property e.g. Null Value=''
But, what is even more concerning is, when the data type is Decimal (15,4), Decimal (38,5) etc.
As decimal data type is fixed width, what it would mean is to give this type of property for each and every decimal type of column.
I wish, new versions come in with some kind of function or more generic property to handle many columns.
Regards,
Kash.
Re: Null handling for Decimal data.
But, what is even more concerning is, when the data type is Decimal (15,4), Decimal (38,5) etc.
Ans: For Decimal ( 15,4) , you can give the Null field length as length+scale i.e. 15+4 = 19
Ans: For Decimal ( 15,4) , you can give the Null field length as length+scale i.e. 15+4 = 19
Re: Null handling for Decimal data.
My understanding is that this is not correct. Length/scale combination (15,4) equates to a total length of 15 of which the mantissa forms 4 e.g.Apotluri wrote:Ans: For Decimal ( 15,4) , you can give the Null field length as length+scale i.e. 15+4 = 19
12345678912.0000
Mark Winter
<i>Nothing appeases a troubled mind more than <b>good</b> music</i>
<i>Nothing appeases a troubled mind more than <b>good</b> music</i>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The required width for Decimal[m,n] is (m+2); m is the number of significant digits, but you also need one character position for the sign (even if it is " ") and one for the decimal placeholder.
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.