|
|
|
Junior Member
 
Group: Forum Members
Last Login: 11/11/2008 3:08:20 PM
Posts: 13,
Visits: 24
|
|
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;
-------------------------------------------------------------------------------
P.H.Padmanabham,
EDW Support Team,
INDIA.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/12/2008 9:19:53 PM
Posts: 469,
Visits: 463
|
|
|
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.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 11/11/2008 3:08:20 PM
Posts: 13,
Visits: 24
|
|
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
P.H.Padmanabham,
EDW Support Team,
INDIA.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/12/2008 9:19:53 PM
Posts: 469,
Visits: 463
|
|
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'
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 8/5/2008 12:35:20 AM
Posts: 2,
Visits: 13
|
|
You have to use varchar instead of Char in Layout.
Thanks 'n' Regards
Nitin Nandeshwar
|
|
|
|
|
Supreme Being
Group: PAC and SFT Members
Last Login: Today @ 8:40:04 AM
Posts: 331,
Visits: 532
|
|
|
LAYOUT describes the input file, not the table. If you use VARTEXT, all the fields must be declared VARCHAR in the layout.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 7/12/2008 11:43:32 AM
Posts: 12,
Visits: 44
|
|
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 ""
In BI, the 90% of the work comes from the 10% of the records.
|
|
|
|