How to elimilate warning when converting string to timestamp
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 73
- Joined: Tue May 10, 2005 6:21 pm
- Location: Sydney
How to elimilate warning when converting string to timestamp
Hope you could help out with my second issue
My job design is like:
sequencial file stage=>transformer=>dataset
the sequencial file is fixed-width, timestamps are in the format: YYYYMMDDHHMISS, for example, 20070227124900 and are filled with spaces where optional.
The SQL Type in the sequencial file stage is VARCHAR(14). I did the conversion in the transformer stage, the logic is like:
1. First trim trailing spaces and store into a stage variable say SVTimestamp
2. if SVTimestamp = '' Then SetNull()
3. If IsValid("Timestamp", StringToTimestamp(SVTimestamp,"%yyyy%mm%dd%hh%nn%ss") Then StringToTimestamp(SVTimestamp,"%yyyy%mm%dd%hh%nn%ss") Else SetNull()
If the incoming timestamps are invalid, e.g. 20070227124961, the conversion in the IsValid will produce a warning in the job log.
Am I right? How can I eliminate the warning from the job log? Appreciate your help.
My job design is like:
sequencial file stage=>transformer=>dataset
the sequencial file is fixed-width, timestamps are in the format: YYYYMMDDHHMISS, for example, 20070227124900 and are filled with spaces where optional.
The SQL Type in the sequencial file stage is VARCHAR(14). I did the conversion in the transformer stage, the logic is like:
1. First trim trailing spaces and store into a stage variable say SVTimestamp
2. if SVTimestamp = '' Then SetNull()
3. If IsValid("Timestamp", StringToTimestamp(SVTimestamp,"%yyyy%mm%dd%hh%nn%ss") Then StringToTimestamp(SVTimestamp,"%yyyy%mm%dd%hh%nn%ss") Else SetNull()
If the incoming timestamps are invalid, e.g. 20070227124961, the conversion in the IsValid will produce a warning in the job log.
Am I right? How can I eliminate the warning from the job log? Appreciate your help.
Edward Yuan
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 73
- Joined: Tue May 10, 2005 6:21 pm
- Location: Sydney
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The quick way is to right click on a warning message and "add rule to message handler". While in that dialog, click Help.
It will be in one of the manuals, but I don't have time to research which one. I'd guess Manager Guide would be the first place I'd look.
It will be in one of the manuals, but I don't have time to research which one. I'd guess Manager Guide would be the first place I'd look.
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: 73
- Joined: Tue May 10, 2005 6:21 pm
- Location: Sydney
The job flow:
sequential file=>transformer=>dataset
The input SQL Type : varchar(14)
The output SQL Type: timestamp
The deviration logic in the transfomer:
If Trim(DSLink10.ColTimestamp) = "" Or IsValid("timestamp", StringToTimestamp(Trim(DSLink10.ColTimestamp) ,"%yyyy%mm%dd%hh%nn%ss")) <> @TRUE Then
SetNull()
Else
StringToTimestamp(Trim(DSLink10.ColTimestamp) ,"%yyyy%mm%dd%hh%nn%ss")
There are 3 input values:
"20020101010161"
"20020101010101"
" " (14 spaces)
The warning I had is for the first record:
APT_CombinedOperatorController,0: Conversion error calling conversion routine timestamp_from_string data may have been lost
I think it is becuase the StringToTimestamp converion in the IsValid function. Can I use the IsValid directly to the string "20020101010161"? Any ideas?
sequential file=>transformer=>dataset
The input SQL Type : varchar(14)
The output SQL Type: timestamp
The deviration logic in the transfomer:
If Trim(DSLink10.ColTimestamp) = "" Or IsValid("timestamp", StringToTimestamp(Trim(DSLink10.ColTimestamp) ,"%yyyy%mm%dd%hh%nn%ss")) <> @TRUE Then
SetNull()
Else
StringToTimestamp(Trim(DSLink10.ColTimestamp) ,"%yyyy%mm%dd%hh%nn%ss")
There are 3 input values:
"20020101010161"
"20020101010101"
" " (14 spaces)
The warning I had is for the first record:
APT_CombinedOperatorController,0: Conversion error calling conversion routine timestamp_from_string data may have been lost
I think it is becuase the StringToTimestamp converion in the IsValid function. Can I use the IsValid directly to the string "20020101010161"? Any ideas?
balajisr wrote:Edward,
Can you post the warning you received?. You should not receive any warning. If the incoming timestamp is invalid then IsValid() function will return 0 and control passes to else condition.
Edward Yuan
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
Edward
you don't need to supress the warning. I have an stupid idea to avoid the warning. just replace your code in transformation to this:
If NOT (IsValid("Timestamp", (DSLink10.ColTimestamp[1,4]:"-":DSLink10.ColTimestamp[5,2]:"-":DSLink10.ColTimestamp[7,2]:" ":DSLink10.ColTimestamp[9,2]:":":DSLink10.ColTimestamp[11,2]:":":DSLink10.ColTimestamp[13,2]))) then SetNull() Else StringToTimestamp(Trim(DSLink10.ColTimestamp) ,"%yyyy%mm%dd%hh%nn%ss")
rather then
If Trim(DSLink10.ColTimestamp) = "" Or IsValid("timestamp", StringToTimestamp(Trim(DSLink10.ColTimestamp) ,"%yyyy%mm%dd%hh%nn%ss")) <> @TRUE Then
SetNull()
Else
StringToTimestamp(Trim(DSLink10.ColTimestamp) ,"%yyyy%mm%dd%hh%nn%ss")
you don't need to supress the warning. I have an stupid idea to avoid the warning. just replace your code in transformation to this:
If NOT (IsValid("Timestamp", (DSLink10.ColTimestamp[1,4]:"-":DSLink10.ColTimestamp[5,2]:"-":DSLink10.ColTimestamp[7,2]:" ":DSLink10.ColTimestamp[9,2]:":":DSLink10.ColTimestamp[11,2]:":":DSLink10.ColTimestamp[13,2]))) then SetNull() Else StringToTimestamp(Trim(DSLink10.ColTimestamp) ,"%yyyy%mm%dd%hh%nn%ss")
rather then
If Trim(DSLink10.ColTimestamp) = "" Or IsValid("timestamp", StringToTimestamp(Trim(DSLink10.ColTimestamp) ,"%yyyy%mm%dd%hh%nn%ss")) <> @TRUE Then
SetNull()
Else
StringToTimestamp(Trim(DSLink10.ColTimestamp) ,"%yyyy%mm%dd%hh%nn%ss")
Last edited by keshav0307 on Tue Feb 27, 2007 3:22 am, edited 1 time in total.
Are you sure it is for the 1st record. I just ran a test job with the 3 values given by you and had no warnings. Do you have only these three values in the file?
20020101010101 is converted into 2002-01-01 01:01:01 and IsValid returns 1.
20020101010161 is not a valid timestamp so it has been converted into null and IsValid returns 0.
Can you set APT_DISABLE_COMBINATION environment to true in your job and post the results.
Ray,
Is there any reason you advocated using message handler?
20020101010101 is converted into 2002-01-01 01:01:01 and IsValid returns 1.
20020101010161 is not a valid timestamp so it has been converted into null and IsValid returns 0.
Can you set APT_DISABLE_COMBINATION environment to true in your job and post the results.
Ray,
Is there any reason you advocated using message handler?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: