On 19 Feb 2013 16:09, A Paul wrote:
update masterf
set DESCR1 = left(descr1, 1) concat '' concat
substr(descr1, 5, 21) concat '' concat
dec( "VALUE" * 100 , 4 )
I get <ed: an overflow> error
<<SNIP>>
I get descr1 correctly fits 30 char in below SQL SELECT and used
same above in UPDATE
The SQL, like most languages, does not care about an /overflow/ of
the length of a string assignment; i.e. the result of the string
expression would be truncated to the size of the shorter receiver [which
in this case, the receiver is a column] to allow the assignment without
an error.
select left(descr1, 1) concat '' concat
substr(descr1, 5, 21) concat '' concat
dec( "VALUE" * 100 , 4 )
from masterf
It appeared to me it could be something wrong with the part of
dec("VALUE" *100, 4) ?
<<SNIP>>
The implication for "overflow" in that expression would indeed be for
that numeric [portion of the overall] expression.
The implication is that, contrary to a prior claim that all of the
values in "VALUE" will never exceed two digits left of the decimal
point, at least one value does exceed two digits of significant digits.
Thus the following query can be used to find the offending RRN(s);
e.g. as presented in a report:
select rrn(a) as OvrFlw, a.*
from masterf as a
where dec(a."VALUE", 9, 2) not between -99.99 and 99.99
/* truncation vs rounding and dec(15, 08) defines "VALUE" column */
BTW, the original expression given had used a character constant of
one blank and another of two blanks\spaces, as the literal strings in
the concatenation expression to achieve the expressed-as-desired
alignment of the character data. While the view of the archive removes
all but one blank, even a copy [as in using copy\paste] taken from the
archived message should have left at least one space instead of the
empty string.? Concatenation of the literal empty-string accomplishes
nothing. To see the actual [HTMLized\munged] expression given in the
UPDATE offered earlier in the thread, use the "view page source" feature
of the browser used to view the following message:
http://archive.midrange.com/rpg400-l/201302/msg00111.html
Note: Another expression discussed previously in this thread, the one
using DIGITS, would have been able to avoid the overflow. But then the
results would be suspect if not outright incorrect. That is, the value
321.25 from a dec(15, 8) concatenated as SUSBSTR(DIGITS("VALUE"), 6, 4)
would effect '2125'. An overflow prevents the incorrect output, because
the UPDATE fails.
It is possible the original scenario is not fully described, or
simply that the number of assumptions that had to be made [and which
were not called-out as errors], are incorrect. Without the scripted DDL
and scripted DML to give an accurate representation of the actual data,
there is great difficulty in providing great answers\solutions.
midrange.com
