Empty Date field Export to Seq File

[trobinson]

I am converting some date fields from our default of 1900-01-01 to the empty string (''). I am writing them out to a sequential stage with a datatype of date nullable and a format of %dd/%mm/%yyyy. They are beng written to the file as **********. I used to do a SetNull on the fields but thought making them all empty would be better. Can I make DataStage stop defaulting empty dates to 10 asterisks or do I need to go back to the SetNull function? Where is this conversion taking place? Why does DS allow all asterisks in preference to an empty string? Ten asterisks is not a valid date.

[ashwin141]

I am converting some date fields from our default of 1900-01-01 to the empty string (''). I am writing them out to a sequential stage with a datatype of date nullable and a format of %dd/%mm/%yyyy. They are beng written to the file as **********. I used to do a SetNull on the fields but thought making them all empty would be better. Can I make DataStage stop defaulting empty dates to 10 asterisks or do I need to go back to the SetNull function? Where is this conversion taking place? Why does DS allow all asterisks in preference to an empty string? Ten asterisks is not a valid date.

Hi Robinson

Probably the problem is in you changing the date to '' and writing it as a date field with the given format . Try writing it as varchar or char. I hope it will work.

Regards
Ashwin

[trobinson]

I prefer not to muck with the definition of the date fields. However, I agree it is probably something to do with defining the output as tick tick. My real question is, granted it works that way, how can I change the default behavior?

[ray.wurlod]

"" is not a valid value for a date data type. Therefore you can not write "" into a date. What you might be able to do is to represent NULL as "" (Null Field Value property) provided you make the output field nullable, and feed it nulls where you want "".

[Andet]

This is actually on AIX 5.2, if it makes a difference.

Ray, or other, do we know what DS does differently when '' is specified instead of "" for a default value? I know what the difference is in C and C++, but not sure in DS/PX.

thanks,

Ande

[trobinson]

It makes no difference, Andet. Nor can one set a stage variable to null. I had to revert to SetNull() in the derivation of the columns. This worked but I no likee.

[kumar_s]

The point is '' is not Null. You made the date column as Nullable but not ''ble . So you will be only null in date field in this scenario.

[alphac]

Hi, just want to give some ideas to you:

You may use empty string ('') to represent a null date when writing
sequential file ( by setting the "Null field value" property )

but when you want to read empty string ('') into a nullable date field,
datastage fatal error :
[ ... cannot have a "null_field" of length 0 on import ...]

so suggest you to use space(10) to represent null date instead of empty
string. Since you could re-use the generated sequential file by Datastage.

Hope it can help you.

[Andet]

so suggest you to use space(10) to represent null date instead of empty
string. Since you could re-use the generated sequential file by Datastage.

That only works if the recipient will accept that. And won't.
nulls work.

[trobinson]

Yes, this is what I do. I set the NULL_VALUE to "" - the EMPTY String. It ONLY works if I NULL the date fields, otherwise the date fields will fill with asterisks. Using space(10) is almost as bad as asterisks. Luckily I use the APT_ALLOW_ZERO_LENGTH_NULL_ON_EXPORT_BLAH_BLAH_BLAH set to TRUE. This will get me away from having to use 10 spaces.

I'm not importing, strictly exporting.

[ray.wurlod]

Import is different depending on whether the source file is delimited or fixed width. If it is delimited, then you CAN have "" as the representation of null.