On 08-Oct-2015 09:20 -0600, 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
Specifically, the above line was apparently the text of the message,
and the error was sqlcode=-407, or:
msg SQL0407 "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.
No. Zero is not the NULL value. And that example data suggests that
the result of the numeric\string expression would become the string of
four zero digit-characters [i.e. '0000'; specifically a known value,
thus NOT NULL], which when implicitly cast by the SQL to a numeric
value, is the value of zero [at least when there is a matching row for
which that expression would be evaluated].
If the Allow Null (ALWNULL) attribute been set for the UMNUM column,
then all unmatched rows would have had the NULL value set, irrespective
the current value of UMNUM. Every row would be updated too, matched or
unmatched, because there is no WHERE clause on the UPDATE. That the
update failed with the -407 [instead of setting probably many values to
NULL] might be cause for rejoice :-)
As Buck suggested, the WHERE A.UMNUM=0 might be intended for that
UPDATE, to update only those rows in ACTFILE that currently have a
zero-value for UMNUM.? The reader does not know the intention, for lack
of any mention, though seems probable that would be the intended effect,
given the predicate A.UMNUM=0 appears in the given subselect. What can
be logically inferred, is just that the issue with unmatched rows is
what is desired be resolved; i.e. as was clearly enough noted, as the
impetus of the OP.
Any idea how to get around this?
The EXISTS predicate is the typical resolution; as already offered by
Satya, and apparently that also was found to be functional.
But the WHERE clause on the UPDATE might also have been preferred, to
have included the ANDed predicate A.UMNUM=0. That would effect the
early omission\filtering of any rows that are never intended to be
updated; i.e. if perhaps, to show explicitly, that non-zero rows should
be left unchanged. Satya's example did not include that additional
predicate. And functionally, that omission is of no consequence. But
performance-wise, including that predicate could be helpful; helpful
also for a reviewer, to infer the intention [by a clearer implication],
having included the additional predicate if knowingly only the existing
zero-values were to be updated from the corresponding non-zero value in
the other file.
So possibly the following serves both functionality and additionally
as [more conspicuously] informative about what rows of the file are to
be updated:
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 /* do not change non-zero values */
and exists
( select '1' /* select anything; one-char is least */
from tstmis/shactfile b
where a.umact = b.umact
and b.umnum > 0
and a.umnum = 0
)
midrange.com
