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;


This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].