Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Location
    California,USA
    Posts
    72

    Unanswered: SUBSTR() error in SP

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have you bothered to read up on the error message? It is really detailed and tells you exactly what is the reason.
    $ 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
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Nov 2003
    Location
    California,USA
    Posts
    72
    DB2 v8.1 FixPak 12
    AIX 5.3.0.0
    Single partition


    But SQL is successful being outside the SP.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Nov 2003
    Location
    California,USA
    Posts
    72
    Finanlly, the cause is, DB2 is misleading. One of the variable DECLAREd is shorter to execute the statement.

    Thank you for all those replied.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •