Solved
SQL0138N
Posted on 2010-08-23
I am using substr function to extract out the string from a column whose datatype is varchar and of 64 in length. Considering 2.5 lacs rows in the table, When i use this function, after fetching out some 1000 rows it throws an error as follws:
SQL0138N A numeric argument of a built-in string function is out of range.
SQLSTATE=22011
SQL0138N A numeric argument of a built-in string function is out of range.
Explanation:
For the SUBSTR function, one of the following conditions exist:
* The value of the second argument of the SUBSTR function is an
expression whose value is less than 1 or greater than M.
* The value of the third argument of the SUBSTR function is an
expression whose value is less than 0 or greater than M-N+1.
For the SUBSTRING function, the following condition exists:
* The value of the second argument of the SUBSTRING function is an
expression whose value is less than 1 or greater than M.
For the LEFT or RIGHT functions, the following condition exists:
* The value of the second argument of the LEFT or RIGHT function is an
expression whose value is less than 0 or greater than the length
attribute of the first argument.
For the INSERT function, one of the following conditions exist:
* The value of the second argument of the INSERT function is an
expression whose value is less than 1 or greater than M + 1.
* The value of the third argument of the INSERT function is an
expression whose value is less than 0 or greater than M-N+1.
For the OVERLAY function, one of the following conditions exist:
* The value of the third argument of the OVERLAY function is an
expression whose value is less than 1 or greater than M + 1.
* The value of the fourth argument of the OVERLAY function is an
expression whose value is less than 0 or greater than M-N+1.
M is the length of the first argument, if it is of fixed length, or M is
the maximum length of the first argument, if it is of varying-length. N
is the value of the second argument.
The statement cannot be executed.
User response:
Ensure that all the numeric arguments of the built-in string function
have legal values according to the above rules.
sqlcode: -138
sqlstate: 22011