I have a problem with loading my data into teradata table using MLoad. I have a text file with a data which is an output from SQL query.
851|73214|2019-01-03|2019-01-03|98.081270|RFF|249872083.40
854|73215|2019-01-03|2019-01-03|98.081270|RFF|355015298.0400
881|96634|2017-05-22|2017-05-22|97.697560|RFF|-6961747.270
and I'm trying to load this data using this mld file:
.LOGTABLE dss.load_DEALS7_log;
.RUN FILE "C:\PASS.TXT";
RELEASE MLOAD dss.DEALS;
drop table dss.DEALS;
create multiset table dss.DEALS
(
FILE_ROWNUM INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE -2147483647 MAXVALUE 100000000 NO CYCLE),
DEAL INTEGER,
EFFECT_DATE VARCHAR(80),
MAT_DATE VARCHAR(80),
CON DECIMAL(28,12),
C_CODE VARCHAR(80),
QUALITY DECIMAL(19,4),
LOAD_DTE DATE FORMAT 'YYYY-MM-DD'
)primary index(FILE_ROWNUM);
.BEGIN MLOAD TABLES dss.DEALS SESSIONS 2;
.LAYOUT FILE;
.FIELD DEAL * VARCHAR(80);
.FIELD EFFECT_DATE * VARCHAR(80);
.FIELD MAT_DATE * VARCHAR(80);
.FIELD CON * VARCHAR(80);
.FIELD C_CODE * VARCHAR(80);
.FIELD QUALITY * VARCHAR(80);
.DML LABEL LOAD;
INSERT INTO dss.DEALS VALUES
('',
:DEAL,
:EFFECT_DATE,
:MAT_DATE,
:CON,
:C_CODE,
:QUALITY,
CURRENT_DATE
);
.IMPORT
INFILE "C:\deals.txt"
LAYOUT FILE
format VARTEXT '|' DISPLAY ERRORS NOSTOP
APPLY LOAD
;
.END MLOAD;
.LOGOFF;
The problem is that the final table is empty and every row is in dss.ET_DEALS table with ErrorCode 2679. I know that the ErrorField is QUALITY, but I don't know why It won't load. The data seems alright. Teradata docs say - "This error occurs when the user submits a numeric-to-character conversion with an illegal format, or when, in converting characters to numeric, either the data or the format contains a bad character." At first I thought That It's because of negative numbers, but then there should only be few rows and not whole input data in ET table. Any help would be greatly appreciated!
I deleted and crated the tables from ground up and also added
.FILLER * VARCHAR(80);in my mld file and It works fine.