sting to time conversion

[samyamkrishna]

Hi,

I have job like described below.

table1-->sort-->join(Left join T1-T2)-->transformer--table3
table2-->sort-->

the tables are teradata tables.

we have a column HEUR_RELV_SOLD_PP which is in time format 'hh:mi:ss' format in table1.

this columns is mapped to table3 through sort,join,transformer.

this column is mapped through all the stages as time[8]
but in transformer stage i am getting a FATAL error

Tr_HISTORIQUE_SOLDE_PREPAYE: Error when checking operator: When binding input interface field "HEUR_RELV_SOLD_PP" to field "HEUR_RELV_SOLD_PP": No default type conversion from type "string[8]" to type "time"

If somebody knows why this fatal error is occuring. please help me out.

Thanks,
Samyam

[ray.wurlod]

You have not specified either a default time format or an explicit time format. Do either or both of these things.

[samyamkrishna]

You have not specified either a default time format or an explicit time format. Do either or both of these things. ...

Hi Ray,

where do we specify either a default time format or an explicit time format
and how do we do it.

It would be great if you could give us a hint on this.

Thanks,
Samyam

[ArndW]

Your transform stage input column HEUR_RELV_SOLD_PP is defined as Char(8), perhaps you inadvertantly redefined it in the sort or join stage. Without an explicit conversion, i.e. StringToTime(), DataStage won't convert it back.

[samyamkrishna]

Your transform stage input column HEUR_RELV_SOLD_PP is defined as Char(8), perhaps you inadvertantly redefined it in the sort or join stage. Without an explicit conversion, i.e. StringToTime(), DataSt ...

hi ArndW,

it is defined as time across all the stages.
It is not defined as character in any stage.

i tried converting it to string and back to time in the transformer.

it dint help.
the error still prvails.

thanks,
samyam

[ArndW]

You've got a converstion somewhere in your stream. Add "APT_DUMP_SCORE" to your job parameters, run it with the value set to "True" and see if you can find references to this column where it might be being redefined.

[chulett]

it is defined as time across all the stages...
which is in time format 'hh:mi:ss'

That's a string, not a Time datatype which is internal while yours is external. Depending on the target, you may need to use StringToTime() to properly convert it... which is exactly what the error you posted is telling you, btw.

[chowdhury99]

Use TO_CHAR function:
TO_CHAR(ColumnName, 'DD-MON-YYYY') )

Thanks

[ray.wurlod]

There is no TO_CHAR function in DataStage.

[ray.wurlod]

The original error message tells you that on the input to Tr_HISTORIQUE_SOLDE_PREPAYE it is a string[8] data type.

Within that Transformer stage use StringToTime() function to convert to time, and specify "%hh:%nn:%ss" as the format string.

[chowdhury99]

Ray, I used TO_CHAR function in Oracle Enterprise stage successfully. Though TO_CHAR is not a datastage function, we can safely use it in Oracle Enterprise Stage.

[ray.wurlod]

The original poster specified "Teradata database" in the additional information field. I doubt that Teradata supports the TO_CHAR function.

Attention to detail is one of the vital attributes that anyone working in data quality and information management requires.

[ManojRawat]

I have faced same issue while taking data from the teradata.Is the column you are picking form teradata table defined as time(8) in the table T1 and T2 . If not you need to cast this to proper time format as per datstge requirement while picking it .

[samyamkrishna]

I have faced same issue while taking data from the teradata.Is the column you are picking form teradata table defined as time(8) in the table T1 and T2 . If not you need to cast this to proper time format as per datstge requirement while picking it .

Hi,

I casted the data from teh source to varchar(8) and the used the string to time function.

Thsi issue got resolved.

Thanks,
Samyam