Null Value handling in seqential file stages in DSEE 7.5.2

[just4geeks]

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!

[ray.wurlod]

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.

[just4geeks]

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.

[balajisr]

In the columns tab of the sequential file, Right click on the column, select edit row and specify null field value for each columns.

[ray.wurlod]

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).

[bakul]

One way of using a common null value for all fields is to read the fields as Varchar instead of char. Later in the job you can turn them back to Char.

[just4geeks]

Thank you very much Ray and Balaji!

I did exactly what you guys said and It solved the problem!!

Warm regards,

[just4geeks]

In the columns tab of the sequential file, Right click on the column, select edit row and specify null field value for each columns.

I am now using Dataset stage as the destination. However, I cannot find the "null field value" when I go to edit row.

Thanks in advance.

[ray.wurlod]

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.

[ks489]

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.

[Apotluri]

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

[miwinter]

Ans: For Decimal ( 15,4) , you can give the Null field length as length+scale i.e. 15+4 = 19

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.

12345678912.0000

[ray.wurlod]

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.

[miwinter]

Cheers Ray, that's helped clarify it once and for all for everyone I think.

Cheers mate :D