Re: update field based on substring of numeric field

On 10/8/2015 10:20 AM, Smith, Mike wrote:

I'm trying to write a sql statement to update a numeric field in tstmis/actfile, but I'm running into a problem with null

Null values not allowed in column or variable UMNUM
.
Below is the query

update tstmis/actfile a
set umnum = (select substring(digits(umnum),6,4)
from tstmis/shactfile b where a.umact = b.umact and b.umnum > 0 and
a.umnum = 0 )

I think the problem is when I have a value like 123450000 which creates a substring of 0000

Any idea how to get around this?

I don't think that's the problem. The error is that NULL is not
allowed, which means the sub-select is returning NULL. The way that
happens is when the WHERE clause is not satisfied, for example you have
an account number which is already populated (a.umnum <> 0) You need to
put an equivalent WHERE clause on the UPDATE as well as where it is on
the sub-select. So maybe:

update tstmis/actfile a
set umnum = (select substring(digits(umnum),6,4)
from tstmis/shactfile b where a.umact = b.umact
and b.umnum > 0
and a.umnum = 0 )
where a.umnum = 0;