reject records needs to be collected in a table

Database
Enthusiast

reject records needs to be collected in a table

Hi Team,

Here's the requirement.

Records needs to be imported into a stage table from file. while loading into table, some records has length error and datatype mismatch columns. 

Currently the script is getting aborted whenever it encounter length/datatype mismatch column records. 

req: the job should not abort instead it has to reject and continue loading valid records (next records). Hence at the end, all the rejected records needs to be collected in a another stage table called error table.

INSERT
INTO ${PRD2_WORK_DB}.r_xyz
(
SCAN_ID
, OFFER_ID
, OFFER_TYPE_CD
, RELATED_OFFER_IND
)
VALUES
(
:SCAN_ID
, :OFFER_ID
, :OFFER_TYPE_CD
, :RELATED_OFFER_IND
);

Can anyone help me with a code snippet to perform this action?

Thank You

Krishna.

2 REPLIES
Teradata Employee

Re: reject records needs to be collected in a table

It's pretty hard to handle bad records caused by length of type error on a table since you hardly determine the datatype you should give to your columns on the error table.

Instead, I'd use tpt (fastload) to load data on staging tables. You can get information here on how errors are handled with fastload  : http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/Load_and_Unload_Utilities/B035_24...

You'll understand that error records will be finally collected on files and need to be treated on a case-by-case basis.

Enthusiast

Re: reject records needs to be collected in a table

Insert all the records into a "staging" table with wide varchar columns, wide enough to absorb your longest data for the column, assume every record is an error record.  From there set your insert query into the desired target table to check the length and, optionally the data type.  The records that are loaded are the "good" records, the ones not loaded are your error records.

Cheers