All Forums Database
31_resu 38 posts Joined 07/13
03 Feb 2016
RDBMS error 3813: The positional assignment list has too many values

Hello, I'm building a TPT script to load a flat file to a table. I'm running it from the command line:
tbuild -f c:\some_dir\tpt_load.txt
My Teradata platform system is: VERSION    14.00.07.15 RELEASE    14.00.07.16
Below is my code dummied up. I'm loading 109 columns of data. The below is as identical as the original, minus all the col names and their commas , etc. The error message suggests that I have a misplaced comma, or the error is in the insert somewhere. I have compared the columns of the target table, to Schma, the Apply, and the Insert, and they align count per count and by row/co, they have their commas, colons, paranthesis, all in the right places an accounted for. So I'm getting the impression that error is misleading and it's bad syntax somewhere other than a column alignment by commas as 3813 is suggesting. Any ideas on what I can try or provide to help resolve? Thank you.
DEFINE JOB LOAD_TPT_STUFF
DESCRIPTION 'LOAD TERADATA TABLE FROM FLAT FILE'
(
DEFINE SCHEMA FFILESCHEMA
DESCRIPTION 'DB.TableName'
(
  col_indx VARCHAR(255)
, StartTime VARCHAR(255)
, Thing VARCHAR(255)
) ;
DEFINE OPERATOR DATACONNECTION
DESCRIPTION 'TPT CONNECTIONS OPERATOR'
TYPE DATACONNECTOR PRODUCER
SCHEMA FFILESCHEMA
ATTRIBUTES
(
  VARCHAR PrivateLogName    = 'tpt_stuff_raw1.log'
, VARCHAR DirectoryPath        = 'c:\some_dir\'
, VARCHAR FileName        = 'data*.csv'
, VARCHAR Format        = 'Delimited'
, VARCHAR TextDelimiter        = ','
, VARCHAR OpenMode        = 'read'
) ;
DEFINE OPERATOR INSERT_TPT_STUFF
DESCRIPTION 'TPT INSERT OPERATOR'
TYPE INSERTER
SCHEMA *
ATTRIBUTES
(
  VARCHAR PrivateLogName    = 'tpt_stuff_raw2.log'
, VARCHAR TdpId            = '127.0.0.1'
, VARCHAR UserName        = 'userid'
, VARCHAR UserPassword        = 'password'
, VARCHAR TargetTable        = 'db.tablename'
, VARCHAR LogTable        = 'db.tablename_log'
, VARCHAR ErrorTable1        = 'db.tablename_err1'
, VARCHAR ErrorTable2        = 'db.tablename_err2'
) ;
 APPLY
( 'INSERT INTO db.tablename
    (
     :col_inds
    ,:StartTime
    ,:Thing
    );
')
TO OPERATOR (INSERT_TPT_CHAT[8])
SELECT
  col_indx
, StartTime
, Thing
FROM OPERATOR
  (DATACONNECTION[8]) ;
) ;
Here is a segment of the error:
(...)
DATACONNECTION[1]: TPT19222 Operator instance 1 processing file 'c:\some_dir\data_date.csv'.
INSERT_TPT_STUFF: connecting sessions
INSERT_TPT_STUFF: The RDBMS retryable error code list was not found
INSERT_TPT_STUFF: The job will use its internal retryable error codes
INSERT_TPT_STUFF: TPT10508: RDBMS error 3813: The positional assignment list hastoo many values.
INSERT_TPT_STUFF: TPT10508: RDBMS error 3813: The positional assignment list hastoo many values.
INSERT_TPT_STUFF: TPT10508: RDBMS error 3813: The positional assignment list hastoo many values.
INSERT_TPT_STUFF: TPT10508: RDBMS error 3813: The positional assignment list hastoo many values.
INSERT_TPT_STUFF: TPT10508: RDBMS error 3813: The positional assignment list hastoo many values.
INSERT_TPT_STUFF: TPT10508: RDBMS error 3813: The positional assignment list hastoo many values.
INSERT_TPT_STUFF: TPT10508: RDBMS error 3813: The positional assignment list hastoo many values.
INSERT_TPT_STUFF: TPT10508: RDBMS error 3813: The positional assignment list hastoo many values.
INSERT_TPT_STUFF: Total Rows Sent To RDBMS:      0
INSERT_TPT_STUFF: Total Rows Applied:            0
INSERT_TPT_STUFF: disconnecting sessions
INSERT_TPT_STUFF: Total processor time used = '2.24641 Second(s)'
INSERT_TPT_STUFF: Start : Wed Feb 03 16:10:58 2016
INSERT_TPT_STUFF: End   : Wed Feb 03 16:10:59 2016
DATACONNECTION[1]: TPT19221 Total files processed: 0.
Job step MAIN_STEP terminated (status 12)
Job userid terminated (status 12)

31_resu 38 posts Joined 07/13
04 Feb 2016

A few more things to add, I added in this to the DEFINE to skip the 1st row header of the flat file:
, VARCHAR SkipRowsEveryFile    = 'Y'
, Integer SkipRows         = 1
And that the target table I'm trying to load with the flat file is empty, and all columns defined as VARCHAR, mostly all varchar(255) with an exception of a few at VARCHAR(8000).
Do I need to mod the target table as well as the define schema datatypes to be more of what is ultimately in the production table? Do certain columns like a date, or a number need to be something other than VARCHAR? I'm basically converting a manual FASTLOAD to TPT, and am still trying to follow some of the process of stage load, cleanup and transformation before ins/sel into a production table.
 
 

Fred 1096 posts Joined 08/04
05 Feb 2016

That error specificallly indicates you have too many commas within the INSERT statement. Check that you don't have any lines with both leading and trailing commas, or extra comma before first or after last expression, duplicated lines, etc.

You must sign in to leave a comment.