Mload error while using Update/Insert for null PI fields

Tools & Utilities

Mload error while using Update/Insert for null PI fields

Hi All,

We are trying to use update else insert loading startegy for a teradata table. The table has PI defined on fields which is nullable. The issue is whenever we are trying to update a record having a null value in any of the PI fields, it is getting rejcted into the UV table (unable to indentify the update and thus trying to do an insert). 

So solve the issue, we have created two DML labels, one for null records and the other for not null. However when we are trying to use two apply statements (with is null and is not null), we are facing error.

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

.DML Label tagDML_Null

Do insert for missing update rows;

UPDATE :CF.DatabaseName.TAB1

SET 

R_ID                                   = :R_ID   , 

WHERE

STAT IS NULL;

INSERT INTO :CF.DatabaseName.TAB1 ( 

R_ID                                  , 

STAT) VALUES(

:R_ID,

NULL);

.DML Label tagDML

Do insert for missing update rows;

UPDATE :CF.DatabaseName.TAB1

SET 

R_ID                                   = :R_ID   , 

WHERE

STAT                                   = :STAT;

INSERT INTO :CF.DatabaseName.TAB1 ( 

R_ID                                  , 

STAT) VALUES(

:R_ID,

:STAT);




Import Infile ':CF.ImportFileName'

 Layout InputFileLayout

 Format Unformat 

 Apply tagDML WHERE STAT IS NULL

 Apply tagDML_Null WHERE STAT IS NOT NULL

;

Can anyone please help me here what can be done to fix the issue?

Thanks,

moloy

2 REPLIES
Enthusiast

Re: Mload error while using Update/Insert for null PI fields

NULL value comparision seems to be causing the issue here. To avoid this try to use coalesce.

Re: Mload error while using Update/Insert for null PI fields

Hi Krishaneesh,

Thank you for your response. We have tried using coalesce function but unfortunately coelesce does not works in the control file APPLY section.

Thanks,

Moloy