Modify error converting Timestamp(Microseconds) to string

[SIHM]

Trying to convert a Timestamp(Microseconds) to string gives the following error

Code: Select all

modify(0),0: Un-handled conversion error on field "DT_DEB_SITUATION " from source type "timestamp[microseconds]" to destination type "string[20]":
source value="2013-01-01 00:00:00.000000"; the result is non-nullable and there is no handle_null to specify a default value.

The schema is being read from a dataset in RCP mode with only DT_DEB_SITUATION defined as

Code: Select all

Data set "/DVT/data/PIL_QPT_DCB/COMMON_DTM/DATASET/Ds_T_CNTC_TJ_ROLE_PRSM_CNTC_01_LAST_IMAGE.ds": 
record
( CLE_DECIBEL: int64;
.
.
  DT_DEB_SITUATION: timestamp[microseconds];
  DT_FIN_SITUATION: nullable timestamp[microseconds];
)

The modify is specified as

Code: Select all

modify '
CLE_JOB:STRING=CLE_DECIBEL;
CLE_DECIBEL=CLE_DECIBEL;
DT_DEB_SITUATION: string[20]=string_from_timestamp[%yyyy-%mm-%dd %hh-%nn-%ss.6](DT_DEB_SITUATION)
NOWARN

Given that the original source is non-nullable and I am defining the string as non nullable, why do I get this error

Additionally, if I specify

Code: Select all

DT_DEB_SITUATION: nullable string[20]=string_from_timestamp[%yyyy-%mm-%dd %hh-%nn-%ss.6]

I no longer get this error.

How can I additionally specify handle_null in the same specification or get Datastage to understand the original value is not nullable.

[priyadarshikunal]

Are you getting error or warning?

whenever you are applying any function to the data.. the output can possibly be null.. (warning) in your case the timestamp format will take more than 20 bytes to store in text format and output not being able to fit in string(20), causing it to default the output may be to a null value (error with not nullable field) or truncate the output and take incorrect data forward. Still you are loosing data in process. Increase the size of that field or do a substring to remove micro seconds and have it in format which fits in 20 character long string.

[ray.wurlod]

You could try nested functions, which sometimes work and sometimes doesn't in the Modify stage. Otherwise you could use two Modify stages; these cannot be adjacent, but you can put a Copy stage between them.

And, yes, do note that to include microseconds your string will need to be at least 26 characters long.

[SIHM]

There is no warning or error given.

I tried to nest the functions but this didn't work.

I will increase the size and accept the fact that although the new field is now nullable, there is no risk of it actually being null.

It does not warrant another modify