DSXchange

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 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??

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??

I mean a hashed file.

A hashed file is also a UV table.

I have no experience with Universe SQL, but it does seem strange that the timestamp in your error message doesn't have any surrounding quote characters:

VALUES ( 9999 , 9009 , 2006-09-13 14:20:55

Mike

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.

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.

I also tryed that. If it is defined as varchar then I get the following error-message:

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"

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.

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.

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.

Do I have to declare the field containing the timestamp as a string (if so, how do I do that?)?

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

Hi,
Try using this
VALUES ( 9999 , 9009 , "2006-08-21 14:30:00.000000").

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

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?

bhaskarjha wrote:Hi,
Try using this
VALUES ( 9999 , 9009 , "2006-08-21 14:30:00.000000").

When I include the double-quotes I get an error-message stating that a unvalid token was found. Without the double-quotes it still states a syntax-error......

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.