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