RE: What column "Character in CAST argument not valid"?

I have the current row it failed on. I went thru those 66 columns and found a conversion error in the data prep. The whole thing just seems unnecessarily complicated.

Thanks




-----Original Message-----
From: Tyler, Matt [mailto:matt.tyler@xxxxxxxxxxxxxx]
Sent: Thursday, March 01, 2018 3:55 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: What column "Character in CAST argument not valid"?

One of two ideas you could try.

1. Create new table same as the source, insert into this table in the order of the first target table you are attempting to insert into. Use this new table as the insert source and where the insert stops at should be the one record before the duplicate.

2. This will be extra work on your part.

Create a view over the table being inserted.
Create an instead of trigger that monitors for insert issues and when it occurs you will have the single insert record that caused the problem and you can record it to another location or produce a message in the job log. The trigger could even be designed to just report the error and skip to the next insert record.

This will be slower to process inserts but you need the values in the database.

Sample from ACS 1.1.7.3
-- category: Data Definition Language (DDL)
-- description: Create or Replace Trigger Instead of Insert create or replace trigger TRIG2
instead of insert on VIEW1 referencing new NEWROW
for each row insert into TABLE1(COLUMN1, COLUMN2) values(NEWROW.COLUMN1, encrypt_rc2(NEWROW.COLUMN2, 'pwd456')

);

-Matt