All Forums Database
kmanivan82 10 posts Joined 02/12
30 Apr 2015
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.

INTO   ${PRD2_WORK_DB}.r_xyz

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

MaximeV 19 posts Joined 11/13
30 Apr 2015

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  :
You'll understand that error records will be finally collected on files and need to be treated on a case-by-case basis.

VandeBergB 182 posts Joined 09/06
30 Apr 2015

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.

Some drink from the fountain of knowledge, others just gargle.

You must sign in to leave a comment.