Initially, all my Sequential File fields were defined as Varchar and I converted them to proper typed fields in a Transformer with StringToDate and StringToTimestamp.
Now I want to redefine them as Date and Time fields in the source, and drop the Transformer stage.
I set the Type defaults / Time / Format string = %hh:%nn but I'm getting the following warning and no rows:
Field "StartTime" has import error and no default value; data: {2 3 : 0 0}, at offset: 27
What's wrong with 23:00 as a Time value in the format %hh:%nn? Here's my full row of data:
"Run Date","Start Date","Start Time","End Date","End Time"
"2010-07-28","2010-07-27","23:00","2010-08-08","23:00"
When I had a Transformer converting the date and time to a Timestamp with StringToTimestamp( in.StartDate : ' ' : in.StartTime, "%yyyy-%mm-%dd %hh:%nn" ) it worked fine.
I've searched and read most of the results, but those are all about nulls or incorrect contents e.g. strings in numerics or trailing delimiters.
One thing I'm not sure I've done right is to do with the Length - I've kept them as they were for the Varchar fields i.e. the StartTime is a Time field with a Length of 5. Is this correct? *update*: it doesn't seem to make any difference if I remove the Length setting.
*Update*: Or, is it just a bad idea to use data types to specify a CSV, should I just make them all Varchar and then use a Modify stage?
*update*: Back to VarChar with a Transformer, couldn't get a Modify stage to recognize "StringToDate" function.
Sequential File - redefining a Varchar as Time
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
Sequential File - redefining a Varchar as Time
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Modify stage functions tend to be expressed as "from", for example date_from_string().
I'm surprised about %hh:%nn not working against 23:00. This might be a bug in the Sequential File stage parser. Are you able to modify the source data so that the time has seconds (23:00:00) as a test?
I'm surprised about %hh:%nn not working against 23:00. This might be a bug in the Sequential File stage parser. Are you able to modify the source data so that the time has seconds (23:00:00) as a test?
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.
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
It's not just the SF parser - '%hh:%nn' doesn't work in a Transformer either. However, there you have the luxury of being able to write StringToTime( in.field : ':00', '%hh:%nn:%ss ) whereas you can't do that in a Sequential File format. So if your input format is immutable at '23:00' then you have to define the SF field as Char or VarChar and then use a Modify or Transformer to convert it. Sub-optimal, IMHO.ray.wurlod wrote:I'm surprised about %hh:%nn not working against 23:00. This might be a bug in the Sequential File stage parser.
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Curiously, possibly not the case.
One of the tuning tips mentioned at IOD 2009 is to read with a Sequential File stage (sequentially) and parse with a Transformer or Column Import stage (in parallel) - this combination often runs faster than parsing in the Sequential File stage.
![Idea :idea:](./images/smilies/icon_idea.gif)
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.