MLOAD script error.

Tools
Enthusiast

MLOAD script error.

Hi Everyone

when i am running following mload script using the '|' delimited source file, i am getting error as

10: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.

Thanks
Paddu
---------------------------------------------
.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_VW
ERRORTABLES ET_PLMWH_EC_PART_VW geadw_tdm_s.UVPLMWH_EC_PART_VW
ERRLIMIT 0
CHECKPOINT 10000
TENACITY 10000
SESSIONS 1
SLEEP 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_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
) 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;
-------------------------------------------------------------------------------
6 REPLIES
Enthusiast

Re: MLOAD script error.

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.
Enthusiast

Re: MLOAD script error.

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.

Thanks
Paddu
Enthusiast

Re: MLOAD script error.

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'

Re: MLOAD script error.

You have to use varchar instead of Char in Layout.
Teradata Employee

Re: MLOAD script error.

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

Re: MLOAD script error.

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