0

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!

neekitit
  • 61
  • 6
  • QUALITY is field #6 and 'RFF' cabn't be converted to a decimal. The 1st field in the input file is not matched in the LAYOUT. – dnoeth Nov 03 '22 at 14:17
  • 1
    I'm super lazy about mload. I would just dump this into a staging table with all the columns defined as varchar, and I would skip the the autoincrement column. I would handle the rest of it using SQL to load to the final table. – Andrew Nov 03 '22 at 14:52
  • Additionally, your sample file has 7 columns, not 6. – Andrew Nov 03 '22 at 14:59
  • @Andrew: Your approach is way better :-) Additionally using FastLoad instead of MLoad (or TPT LOAD instead of legacy FL) – dnoeth Nov 03 '22 at 15:04
  • @Andrew Ok. That makes sense. So I guess that I only need to add the ID column for FILE_ROWNUM in my layout and insert and It should run. Did I get it right ? I'll probably rebuild this in some time, but there are like 30 MLoad files and all are constructed like this. That's the beauty of overtaking someone's tasks. ;) – neekitit Nov 03 '22 at 15:14
  • Start the layout with `.FILLER * VARCHAR(80);` to skip FILE_ROWNUM in the input. – dnoeth Nov 03 '22 at 15:18
  • @dnoeth I added the line that you gave me, but I still get the same error. – neekitit Nov 03 '22 at 15:33
  • Still the same error 2679 for QUALITY? Did you add the FILLER as 1st field? – dnoeth Nov 03 '22 at 16:54
  • @dnoeth Yeah I added It like this .BEGIN MLOAD TABLES dss.DEALS SESSIONS 2; .LAYOUT FILE; .FILLER * VARCHAR(80); .FIELD DEAL * VARCHAR(80); and I still get the same error – neekitit Nov 04 '22 at 11:17
  • Not sure you can MLOAD a table where the PI is GENERATED ALWAYS. Generally MLOAD requires the client to supply the PI values. Not sure why 2679 though. You could try using a column name list in the INSERT instead of positional form. – Fred Nov 04 '22 at 14:33
  • @Fred the weird thing is that this is the only mload that doesn't work and others are 1:1 to this. The only thing that is different between them are column names. I checked the input data few times and everything seems also fine. It's really weird. – neekitit Nov 05 '22 at 20:29

1 Answers1

0

I deleted and crated the tables from ground up and also added .FILLER * VARCHAR(80); in my mld file and It works fine.

neekitit
  • 61
  • 6