handling nulls in timestamp

dsdev_123 · Post by **dsdev_123** » Tue Nov 06, 2007 4:12 pm

hi i have a problem where i have a column SHP_DT which is a time stamp and i have to handle null and defualt it to a constant value by using modify stage

i was trying
SHP_DT(outcolumn)=handle_null(SHP_DT, " ")
its throwing error saying it is a bad literal
can any body throw some light

thanks

mhester · Post by **mhester** » Tue Nov 06, 2007 4:53 pm

I believe you can add the environment variable -

APT_IMPEXP_ALLOW_ZERO_LENGTH_FIXED_NULL.

From the Parallel Job Advanced User Guide -

When set, allows zero length null_field value with fixed length fields. This
should be used with care as poorly formatted data will cause incorrect
results. By default a zero length null_field value will cause an error.

Timestamps are fixed width and this should take care of your problem.

Regards,

mhester · Post by **mhester** » Tue Nov 06, 2007 5:11 pm

I think I missed the mark on my previous post. This would work when defined in the schema and read via a stage. Obviously you are past that point and using the modify stage.

Sorry

throbinson · Post by **throbinson** » Wed Nov 07, 2007 6:25 am

You must modify a NOT NULLABLE column to be within the domain of the defined datatype. A column defined as a NOT NULL timestamp must be a timestamp. A space or empty is not a valid timestamp.
Can you use a default out of bounds value that is a Timestamp? Like 9999-12-31 23:59:59? (Teradata) to represent NULL? BTW - your default format may be different. Or can you define it as nullable? Or as a varchar?