TPT02638: Error: Conflicting data length for column

Tools & Utilities
Teradata Employee

TPT02638: Error: Conflicting data length for column

Hi,

 

I am facing an error while using TPT to extract a file which previously was working fine in production. The only change I made was to remove a semicolon from column using oreplace in view definition.

 

oreplace (LHRR.Request_Data_Txt,';','') AS Request_Data_Txt

 

As per my understanding, this shouldn't have changed column length itself. But I'm now getting following error.

 

TPT_INFRA: TPT02638: Error: Conflicting data length for column(26) - Request_Data_Txt. Source column's data length (1000) Target column's data length (8000).

Where can I find this Target column and what has defined its length?

 

TPT script is as follows: 

 

DEFINE JOB EXPORT_TO_FILE
DESCRIPTION 'EXPORT SAMPLE ROWS from TABLE TO A FILE'
(
DEFINE SCHEMA Accounts_SCHEMA
DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA'
(
HOUSEHOLD_ID BIGINT
, PARTY_ID BIGINT
, POLICY_ID BIGINT
, PARTY_TYPE VARCHAR(1)
, Prospect_Ind BYTEINT
, Prospect_Id BIGINT
, Communication_id VARCHAR(12)
, Communication_Name VARCHAR(40)
, SEGMENT_ID VARCHAR(12)
, Segment_Name VARCHAR(40)
, Lead_Key_id VARCHAR(18)
, Selection_Dttm TIMESTAMP(6)
, Selection_Group_Ord BIGINT
, Step_Id VARCHAR(12)
, Step_Name VARCHAR(40)
, Step_Dttm TIMESTAMP(6)
, Collateral_Id VARCHAR(12)
, Collateral_Name VARCHAR(40)
, Channel_Class_Id VARCHAR(12)
, Channel_Class_Name VARCHAR(40)
, Channel_Instance_Id VARCHAR(12)
, Channel_Instance_Name VARCHAR(40)
, Response_Id VARCHAR(12)
, Response_Name VARCHAR(40)
, Response_Dttm TIMESTAMP(6)
, Request_Data_Txt VARCHAR(8000)
, Invalid_Response_Reason_Cd SMALLINT
, Invalid_Response_Reason_Cd_Desc VARCHAR(1000)
, STATUS_DTTM TIMESTAMP(6)
, Extract_Dttm VARCHAR(14)
, Response_Address VARCHAR(150)
, Disposition_CD SMALLINT
);

DEFINE OPERATOR FILE_WRITER()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'
TYPE DATACONNECTOR CONSUMER
SCHEMA Accounts_SCHEMA
ATTRIBUTES
(
VARCHAR FileName = '/mnt/sftp/0414rsa/outgoing/General/CIM_RESPONSE_HISTORY.csv',
VARCHAR IndicatorMode = 'N',
VARCHAR OpenMode = 'Write',
VARCHAR Format = 'Delimited',
VARCHAR TextDelimiter = ';'
);

DEFINE OPERATOR Export_Accounts
TYPE EXPORT
SCHEMA Accounts_SCHEMA
ATTRIBUTES
( VARCHAR UserName=@UserName,
VARCHAR UserPassword=@UserPassword,
VARCHAR Tdpid=@TDPId,
INTEGER MaxSessions=4,
INTEGER TenacitySleep=1,
VARCHAR SelectStmt =
'SELECT Household_Id, Party_Id, Policy_Id, Party_Type, Prospect_Ind,
Prospect_Id, Communication_id, Communication_Name, SEGMENT_ID,
Segment_Name, Lead_Key_id, Selection_Dttm, Selection_Group_Ord,
Step_Id, Step_Name, Step_Dttm, Collateral_Id, Collateral_Name,
Channel_Class_Id, Channel_Class_Name, Channel_Instance_Id, Channel_Instance_Name,
Response_Id, Response_Name, Response_Dttm, Request_Data_Txt, Invalid_Response_Reason_Cd,
Invalid_Response_Reason_Cd_Desc, Status_Dttm, Extract_Dttm, Response_Address, Disposition_CD
FROM CIM_CUSTDATA_VIEW.CUBE_CIM_RESPONSE_HISTORY_TST
ORDER BY Communication_id
;'
);

APPLY TO OPERATOR (File_Writer[1])

SELECT
Household_Id, Party_Id, Policy_Id, Party_Type, Prospect_Ind,
Prospect_Id, Communication_id, Communication_Name, SEGMENT_ID,
Segment_Name, Lead_Key_id, Selection_Dttm, Selection_Group_Ord,
Step_Id, Step_Name, Step_Dttm, Collateral_Id, Collateral_Name,
Channel_Class_Id, Channel_Class_Name, Channel_Instance_Id, Channel_Instance_Name,
Response_Id, Response_Name, Response_Dttm, Request_Data_Txt, Invalid_Response_Reason_Cd,
Invalid_Response_Reason_Cd_Desc, Status_Dttm, Extract_Dttm, Response_Address, Disposition_CD
FROM OPERATOR (Export_Accounts[1]);

);

 

Tags (3)
3 REPLIES
Teradata Employee

Re: TPT02638: Error: Conflicting data length for column

OREPLACE is returning VARCHAR(4000) CHARACTER SET UNICODE (check the manual), hence the 8000 bytes in the error message.

When you use OREPLACE, OTRANSLATE, RegExp functions, etc. you may need to explicitly CAST the result back to the intended / original size.

Teradata Employee

Re: TPT02638: Error: Conflicting data length for column

Thanks Fred, didn't know Oreplace was changing the row length after removing the semicolon.

Won't explicitly casting the column result in data loss, what does it append to the row after applying the function and where did you see its 4000 new length?

Teradata Employee

Re: TPT02638: Error: Conflicting data length for column

It's not the actual data length; it's the declared maximum possible data length for the function result that is causing this behavior.

 

Actually, it may be allowing for 8000 LATIN characters rather than 4000 UNICODE characters. I was basing it on the description of the problem and general behavior of UDFs (and experience with OREPLACE, RegExp functions, etc.).