RDBMS error 3813: The positional assignment list has too many values

Database

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)

2 REPLIES

Re: RDBMS error 3813: The positional assignment list has too many values

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.

Teradata Employee

Re: RDBMS error 3813: The positional assignment list has too many values

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.