SUBSTR() error in SP

nagasurir · #1 (**permalink**) 07-16-08, 13:39

When I execute the SQL with lots of Global Temporary Tables, it works fine.
When I call the stored procedure having the same SQL, it throws the below error.
SQL0138N: The second or third argument of the SUBSTR function is out of range.
Can any shed some light on it.

This is killing my time

Thanks

n_i · #2 (**permalink**) 07-16-08, 13:53

Quote:

Originally Posted by nagasurir

This is killing my time

So now you decided to kill our time too by not providing any details about your system and the actual SQL?

stolze · #3 (**permalink**) 07-16-08, 14:06

Have you bothered to read up on the error message? It is really detailed and tells you exactly what is the reason.

Quote:

$ db2 "? SQL0138N"

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

nagasurir · #4 (**permalink**) 07-16-08, 14:52

DB2 v8.1 FixPak 12
AIX 5.3.0.0
Single partition

But SQL is successful being outside the SP.

stolze · #5 (**permalink**) 07-17-08, 03:14

Did you read the message description? It says that the offset and length for the SUBSTR were out of bounds. So you should verify how long the string value really is and which offset/lengths you try to apply to it.

nagasurir · #6 (**permalink**) 07-17-08, 16:17

Finanlly, the cause is, DB2 is misleading. One of the variable DECLAREd is shorter to execute the statement.

Thank you for all those replied.

Internet Services

Web Development

Digital Marketing

Consumer Tech