Mload Update: Records are not getting updated

Tools

Mload Update: Records are not getting updated

Hello All,

I am running a mload job. In the source file, the fields are having different data types like Decimal, Char & Varchar. But when i am running the job, major volume of records are going to error tables with error code 2797 (saying MLoad MARK MISSING UPDATE). That means its not finding records with given where conditions.

But when i am taking a sample record out of those records from error table and executing with the same combinations as previous where conditions in SQL assistant, I can see already records are existing in target table. Then why it didn't find it in mload update.. 

So I am doubting some discrepancy in in_type_cd column which is having CHAR data type. Do we need to do any special handling for CHAR fields??

---------------------------------------------------------

Primary Index is emp_no

---------------------------------------------------------

.LAYOUT datalayout INDICATORS;

.FIELD set_flag  1 CHAR(1);

.FIELD in_emp_no  * DECIMAL(18);

.FIELD in_dt_tm  * CHAR(19);

.FIELD in_type_cd  * CHAR(10);

.FIELD in_line_id  * VARCHAR(10);

-----------------------------------------------------------

.DML LABEL test_dml mark duplicate update rows;

UPDATE TABLE_ABC

     SET

     start_dt_tm    =:in_dt_tm,

     type_cd     =:in_type_cd,

     line_id           =:in_line_id,

     WHERE emp_no                    =:in_emp_no

     and start_dt_tm      =:in_dt_tm

     and type_cd    =:in_type_cd

     and (line_id                  =:in_line_id

       or  line_id                = 'CRIC'

       or  line_id                = 'HOCK'

       or  line_id                = 'TEN');

--------------------------------------------------------------

Thanks

Abhi

1 REPLY

Re: Mload Update: Records are not getting updated

You're not really setting anything new here, other than the "or"  lines in the where clause.  Your query says set the start_dt_tm = :in_dt_tm, type_cd = :in_type_cd and line_id = :in_line_id  and you set those as equality conditions in the where clause.  

So it's never going to change anything, other than possibly setting the line id to "CRIC","HOCK" or "TEN".  Updating the business keys where they're equal won't do anything.

perhaps, 

Set line_id = :in_line_id

where 

emp_no=:in_emp_no

and start_dt_tm = :in_dt_tm

and type_cd = :in_type_cd

where line_id in ('CRIC','HOCK','TEN');