All Forums Tools
Paddu 14 posts Joined 09/07
13 Jun 2008
MLOAD script error.

Hi Everyone when i am running following mload script using the '|' delimited source file, i am getting error as10:23:04 UTY1419 The field type for field PART is not allowed when using the VARTEXT record format.Not able to understand whats the issue. Please help me.Script overview:Read data from a '|' delimited file and loads into a table using the mload script.ThanksPaddu------------------------------------ ---------.DATEFORM ANSIDATE;.LOGON database/username,password;.LOGTABLE ML_PLMWH_EC_PART_VW;DROP TABLE ET_PLMWH_EC_PART_VW ;DROP TABLE UV_PLMWH_EC_PART_VW ;DROP TABLE WT_PLMWH_EC_PART_VW ;.ROUTE MESSAGES WITH ECHO TO FILE '/data/informatica/tdm/SessLogs/ PLMWH_EC_PART_VW.out.ldrlog' ;.BEGIN IMPORT MLOAD TABLES PLMWH_EC_PART_VW WORKTABLES WT_PLMWH_EC_PART_VWERRORTABLES ET_PLMWH_EC_PART_VW geadw_tdm_s.UVPLMWH_EC_PART_VWERRLIMIT 0CHECKPOINT 10000TENACITY 10000SESSIONS 1SLEEP 6; /* Begin Layout Section */ .Layout InputFileLayout;.Field PART * char(100) ;.Field REVISION * char(100);.Field VAULT * char(100);.Field SOURCE_MODIFIED_DATE * char(100);.Field SOURCE_MODIFIED_DATE_DATE* DATE;.Field SOURCE_MODIFIED_DATE_TIME * char(100);.Field SOURCE_ORIGINATED_DATE * char(100);.Field SOURCE_ORIGINATED_DATE_DATE * DATE;.Field SOURCE_ORIGINATED_DATE_TIME * char(100);.Field OWNER * char(100);.Field POLICY * char(100);.Field STATE * char(100);.Field FIRST_REVISION * char(100);.Field LATEST_REVISION * char(100);.Field OBJECT_ID * char(100);.Field CREATED_BY * char(100);.Field CREATION_DATE * char(100);.Field CREATION_DATE_DATE * DATE;.Field CREATION_DATE_TIME * char(100);.Field LAST_UPDATED_BY * char(100);.Field LAST_UPDATE_DATE * char(100);.Field LAST_UPDATE_DATE_DATE * DATE;.Field LAST_UPDATE_DATE_TIME * char(100);/* End Layout Section */ /* begin DML Section */ .DML Label tagDML;INSERT INTO PLMWH_EC_PART_VW ( PART,REVISION,VAULT,SOURCE_MODIFIED_DATE,SOURCE_MODIFI ED_DATE_DATE,SOURCE_MODIFIED_DATE_TIME,SOURCE_ORIGINAT ED_DATE ,SOURCE_ORIGINATED_DATE_DATE,SOURCE_ORIGINATED_DATE_TI ME,OWNER,POLICY,STATE,FIRST_REVISION,LATEST_REVISION,O BJECT_ID,CREATED_BY,CREATION_DATE,CREATION_DATE_DATE,C REATION_DATE_TIME,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAS T_UPDATE_DATE_DATE,LAST_UPDATE_DATE_TIME) VALUES ( :PART,:REVISION,:VAULT,:SOURCE_MODIFIED_DATE,: SOURCE_MODIFIED_DATE_DATE,:SOURCE_MODIFIED_DATE_TIME,: SOURCE_ORIGINATED_DATE ,:SOURCE_ORIGINATED_DATE_DATE,: SOURCE_ORIGINATED_DATE_TIME,:OWNER,:POLICY,:STATE,: FIRST_REVISION,:LATEST_REVISION,:OBJECT_ID,: CREATED_BY,:CREATION_DATE,:CREATION_DATE_DATE,: CREATION_DATE_TIME,:LAST_UPDATED_BY,: LAST_UPDATE_DATE,:LAST_UPDATE_DATE_DATE,: LAST_UPDATE_DATE_TIME) ; /* end DML Section */ /* Begin Import Section */ .Import Infile '/data/informatica/tdm/SrcFiles/PLMWH_EC_PART_VW.dat' Format VARTEXT '|' Layout InputFileLayout Apply tagDML ;/* End Import Section */ .END MLOAD;.LOGOFF;---------------------------------------- ---------------------------------------

joedsilva 505 posts Joined 07/05
13 Jun 2008

you have to use VARCHAR in the layout instead of CHAR when using VARTEXT as the format. your tables columns could be still CHAR only the layout has to be updated.

Paddu 14 posts Joined 09/07
13 Jun 2008

Yes.. thanks for your reply. It works.Now i am getting another error. I have changed every thing to varchar. but in source file i have data, time and timestamp values similary corresponding data,float and timestamp data type declaration in the Database.Record is reading from the script but it is inserting into error table not in main table.Its showing me 2679 errorcode. not able to understand how to convert this.ThanksPaddu

joedsilva 505 posts Joined 07/05
13 Jun 2008

It could be happening because the format of your input data field format is not matching what is expected by teradata. You can experiment by adding a formating clause in your insert to accurately represent the format of your input data field.for example if timestamp field is in the format 03/22/2007 12:33:12 then format the input field as 'MM/DD/YYYYbHH:mi:SS'

nandeshn 2 posts Joined 06/08
25 Jun 2008

You have to use varchar instead of Char in Layout.

Fred 1096 posts Joined 08/04
26 Jun 2008

LAYOUT describes the input file, not the table. If you use VARTEXT, all the fields must be declared VARCHAR in the layout.

oalfonso 12 posts Joined 02/08
12 Jul 2008

Teradata should put your sentence in ARIAL 98 in the pdfs, I don't know how many questions are all through internet with this problem, and in my work every week someone asks me ""

You must sign in to leave a comment.