CODEWITHSUNDEEP
informaticainformatica-powercenterinformatica-cloud
Bond
Bond

Reputation: 25

cannot place a null value in a not null field

I am having the error message cannot place a null value in a not null field. In the target table, the not null fields I have are 'INC' and this is my IF statement is

IIF(LTRIM(RTRIM(SUBSTR(HEADER_INFO,3,15)))='',NULL,LTRIM(RTRIM(SUBSTR(HEADER_INFO,3,15))))

How can I write this IF statement so that it does not send any null values. The second field is the file load ID which gets generated which I think it's ok based on the information in the file load table.

Upvotes: 0

Views: 1770

Answers (1)

Jim Macaulay
Jim Macaulay

Reputation: 5155

You cannot insert null values to the table having not null constraints. In your function you are checking for null as well as retrieving the result also as null. Instead of NULL, provide any default value.

IIF(LTRIM(RTRIM(SUBSTR(HEADER_INFO,3,15)))='',Default_Value, LTRIM(RTRIM(SUBSTR(HEADER_INFO,3,15))))

Or you can use Filter transformation and restrict the null values to flow into the target

Upvotes: 1

Related Questions