Insert Timestamp ("YYYY:MM:DD HH:MM:SS") in hashta
Moderators: chulett, rschirm, roy
Insert Timestamp ("YYYY:MM:DD HH:MM:SS") in hashta
Hello,
I am having trouble inserting a record into a hashtable with a routine. This record contains a field TIMESTAMP. I get an error-message on the data-type of this field. This is the actual error-message:
DataStage/SQL: character and number types are incompatible in this operation
Scanned command was INSERT INTO ERROR_LOG_hash ( JOB_ID , ERROR_CODE , TIMESTAMP , TIME , PROCESS_ID , SOURCE_TABLE , TARGET_TABLE , SUBS_CODE , VALUE_KEY1 , VALUE_KEY2 , VALUE_KEY3 , VALUE_KEY4 , VALUE_KEY5 , VALUE_ID1 , VALUE_ID2 , VALUE_ID3 , VALUE_ID4 , VALUE_ID5 , VALUE_ERR_ATTR1 , VALUE_ERR_ATTR2 , VALUE_ERR_ATTR3 , ERROR_DESCRIPTION ) VALUES ( 9999 , 9009 , 2006-09-13 14:20:55
DataStage/SQL: Field "TIMESTAMP" data type does not match insert value.
The hashtable is created in another DS-job. TIMESTAMP is defined Timestamp 23-3. I have also tryed varchar but nothing works.
I use this statement to insert:
Command = 'INSERT INTO ERROR_LOG_hash (JOB_ID, ERROR_CODE, TIMESTAMP, TIME, PROCESS_ID, SOURCE_TABLE, TARGET_TABLE, SUBS_CODE, VALUE_KEY1, VALUE_KEY2, VALUE_KEY3, VALUE_KEY4, VALUE_KEY5, VALUE_ID1, VALUE_ID2, VALUE_ID3, VALUE_ID4, VALUE_ID5, VALUE_ERR_ATTR1, VALUE_ERR_ATTR2, VALUE_ERR_ATTR3, ERROR_DESCRIPTION) VALUES(':JobID:', ':ErrorCode:', ':Timestamp:', ':Time:', ':ProcessName:', ':SourceTable:', ':TargetTable:', ':SubsCode:', ':ValueKey1:', ':ValueKey2:', ':ValueKey3:', ':ValueKey4:', ':ValueKey5:', ':ValueID1:', ':ValueID2:', ':ValueID3:', ':ValueID4:', ':ValueID5:', ':ErrAttr1:', ':ErrAttr2:', ':ErrAttr3:', ':ErrorDescription:');'
Call DSExecute('TCL', Command, Output, Code)
Can someone help me to solve this problem?
I am having trouble inserting a record into a hashtable with a routine. This record contains a field TIMESTAMP. I get an error-message on the data-type of this field. This is the actual error-message:
DataStage/SQL: character and number types are incompatible in this operation
Scanned command was INSERT INTO ERROR_LOG_hash ( JOB_ID , ERROR_CODE , TIMESTAMP , TIME , PROCESS_ID , SOURCE_TABLE , TARGET_TABLE , SUBS_CODE , VALUE_KEY1 , VALUE_KEY2 , VALUE_KEY3 , VALUE_KEY4 , VALUE_KEY5 , VALUE_ID1 , VALUE_ID2 , VALUE_ID3 , VALUE_ID4 , VALUE_ID5 , VALUE_ERR_ATTR1 , VALUE_ERR_ATTR2 , VALUE_ERR_ATTR3 , ERROR_DESCRIPTION ) VALUES ( 9999 , 9009 , 2006-09-13 14:20:55
DataStage/SQL: Field "TIMESTAMP" data type does not match insert value.
The hashtable is created in another DS-job. TIMESTAMP is defined Timestamp 23-3. I have also tryed varchar but nothing works.
I use this statement to insert:
Command = 'INSERT INTO ERROR_LOG_hash (JOB_ID, ERROR_CODE, TIMESTAMP, TIME, PROCESS_ID, SOURCE_TABLE, TARGET_TABLE, SUBS_CODE, VALUE_KEY1, VALUE_KEY2, VALUE_KEY3, VALUE_KEY4, VALUE_KEY5, VALUE_ID1, VALUE_ID2, VALUE_ID3, VALUE_ID4, VALUE_ID5, VALUE_ERR_ATTR1, VALUE_ERR_ATTR2, VALUE_ERR_ATTR3, ERROR_DESCRIPTION) VALUES(':JobID:', ':ErrorCode:', ':Timestamp:', ':Time:', ':ProcessName:', ':SourceTable:', ':TargetTable:', ':SubsCode:', ':ValueKey1:', ':ValueKey2:', ':ValueKey3:', ':ValueKey4:', ':ValueKey5:', ':ValueID1:', ':ValueID2:', ':ValueID3:', ':ValueID4:', ':ValueID5:', ':ErrAttr1:', ':ErrAttr2:', ':ErrAttr3:', ':ErrorDescription:');'
Call DSExecute('TCL', Command, Output, Code)
Can someone help me to solve this problem?
Kind regards,
Johannes Drost
Johannes Drost
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
Re: Insert Timestamp ("YYYY:MM:DD HH:MM:SS") in ha
I m little confused..When you say HashTable do you mean hashed File or a DB table?? So Job1 loads a tables and the Job2 Loads the file??
Re: Insert Timestamp ("YYYY:MM:DD HH:MM:SS") in ha
I mean a hashed file.DeepakCorning wrote:I m little confused..When you say HashTable do you mean hashed File or a DB table?? So Job1 loads a tables and the Job2 Loads the file??
Kind regards,
Johannes Drost
Johannes Drost
I also tryed that. If it is defined as varchar then I get the following error-message:kduke wrote:It is so much easier to make all the columns varchar on a hashed file. Deal with timestamps only when writing to a real table.
DataStage/SQL: syntax error. Unexpected symbol. Token was "2006-08-21 14:30".
Scanned command was INSERT INTO ERROR_LOG_hash6 ( JOB_ID , ERROR_CODE , TIMESTAMP , TIME , PROCESS_ID , SOURCE_TABLE , TARGET_TABLE , SUBS_CODE , VALUE_KEY1 , VALUE_KEY2 , VALUE_KEY3 , VALUE_KEY4 , VALUE_KEY5 , VALUE_ID1 , VALUE_ID2 , VALUE_ID3 , VALUE_ID4 , VALUE_ID5 , VALUE_ERR_ATTR1 , VALUE_ERR_ATTR2 , VALUE_ERR_ATTR3 , ERROR_DESCRIPTION ) VALUES ( 9999 , 9009 , "2006-08-21 14:30"
Kind regards,
Johannes Drost
Johannes Drost
I have changed the field-names (correct remark), but I still get the same error-message. I tryed with and without double-quote " in the string.WoMaWil wrote:maybe that to mask or not to mask or to convert or not to convert is the hash files problem...
Try to change the field-names "TIMESTAMP" and "TIME " as fieldname make my stomach turn a bid.
Use "ErrorTimeStamp" and "ErrorTime" and tell if it helped.
Do I have to declare the field containing the timestamp as a string (if so, how do I do that?)?
Kind regards,
Johannes Drost
Johannes Drost
use string
I do not know how you insert timestamp into hashtable. I just did a test, wrtie timestamp as string into hash file, the field data type is timestamp. There is no problem at all. the timestamp I am using is like this, yyyymmddhhmmss, for exsample 20060606124731
-
- Participant
- Posts: 67
- Joined: Thu Apr 06, 2006 7:13 am
Re: use string
Can you send me the code of this test, because I tryed it and it wouldn't work. Do you declare the timestamp-variable in the routine as string (how?) or do you just assign a alphanumeric value to it?changming wrote:I do not know how you insert timestamp into hashtable. I just did a test, wrtie timestamp as string into hash file, the field data type is timestamp. There is no problem at all. the timestamp I am using is like this, yyyymmddhhmmss, for exsample 20060606124731
Kind regards,
Johannes Drost
Johannes Drost
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hashed files do not support the TimeStamp data type. Prefer VarChar with a precision at least as large as you need (19, 23, 26 or more). You will need to enclose the value in single quotes.
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.