IncLotsHashToTable..Dstage: ORA-01400: cannot insert NULL in

[ysrini9]

Hi All,

Our Datastage jobs are got aborted couple of days becuase of this below problem.

IncLotsHashToTable..Dstage: ORA-01400: cannot insert NULL into coulmns

but As this is a not null column and since null values are trying to get inserted we are facing this problem.

in that column we did constraint level not(isnull(...like that)
even jobs are getting aborted.
How to solve this problem
Regards
srini

[loveojha2]

in that column we did constraint level not(isnull(...like that)
even jobs are getting aborted.

Can you paste what derivation you provided in this column. and also constraint are not column level they are set at the row level.

[Mike]

Oracle will interpret an empty string ('') as NULL. In DataStage an empty string is just an empty string.

Mike

[DSguru2B]

for null check for isnull() = 0 and for empty string use the len() function. By those two functions you can handle it.

[ysrini9]

Hi
we are using Constriant like

Linkname.coulmnname =0 OR Linkname.coulmnname =0 Or Linkname.coulmnname =0

is this way is possible to handle nullvalues into notnull column.

Regards
srini

Oracle will interpret an empty string ('') as NULL. In DataStage an empty string is just an empty string.

Mike

[DSguru2B]

Hi
Linkname.coulmnname =0 OR Linkname.coulmnname =0 Or Linkname.coulmnname =0

That will not handle nulls. If you want to filter out the null values you need something like

Code: Select all

ISNULL(Linkname.coulmnname1) = 0 OR LEN(trim(Linkname.coulmnname1)) <> 0 AND ISNULL(Linkname.coulmnname2) = 0 OR LEN(trim(Linkname.coulmnname2)) <> 0 AND .....<so on>