Page 1 of 1
IncLotsHashToTable..Dstage: ORA-01400: cannot insert NULL in
Posted: Wed Jun 28, 2006 8:51 am
by 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
Posted: Wed Jun 28, 2006 8:58 am
by 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.
Posted: Wed Jun 28, 2006 9:14 am
by Mike
Oracle will interpret an empty string ('') as NULL. In DataStage an empty string is just an empty string.
Mike
Posted: Wed Jun 28, 2006 9:23 am
by DSguru2B
for null check for isnull() = 0 and for empty string use the len() function. By those two functions you can handle it.
Notnull coulmn trying to insert null
Posted: Wed Jul 05, 2006 8:01 am
by 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
Mike wrote:Oracle will interpret an empty string ('') as NULL. In DataStage an empty string is just an empty string.
Mike
Re: Notnull coulmn trying to insert null
Posted: Wed Jul 05, 2006 8:06 am
by DSguru2B
ysrini9 wrote: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>