sting to time conversion
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 258
- Joined: Tue Jul 04, 2006 10:35 pm
- Location: Toronto
sting to time conversion
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 258
- Joined: Tue Jul 04, 2006 10:35 pm
- Location: Toronto
Hi Ray,ray.wurlod wrote:You have not specified either a default time format or an explicit time format. Do either or both of these things. ...
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
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.
Last edited by ArndW on Fri Apr 16, 2010 3:26 am, edited 1 time in total.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 258
- Joined: Tue Jul 04, 2006 10:35 pm
- Location: Toronto
ArndW wrote: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
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.it is defined as time across all the stages...
which is in time format 'hh:mi:ss'
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 43
- Joined: Thu May 29, 2008 8:41 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Within that Transformer stage use StringToTime() function to convert to time, and specify "%hh:%nn:%ss" as the format string.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 43
- Joined: Thu May 29, 2008 8:41 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Attention to detail is one of the vital attributes that anyone working in data quality and information management requires.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 2
- Joined: Wed Oct 21, 2009 7:17 am
-
- Premium Member
- Posts: 258
- Joined: Tue Jul 04, 2006 10:35 pm
- Location: Toronto
ManojRawat wrote: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