Solved

SQL0138N

Posted on 2010-08-23
6
1,224 Views
Last Modified: 2012-05-10
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



0
Comment
Question by:mohitgyl
  • 3
  • 3
6 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 33502064
this means the values you psas in the substr function are illegal,
for example if the string you  work on is of length 10, and you choose to start at character 11
for example

select substr('0123456789', 12, 2)

check your data, or post here your code and i'll try to help
0
 

Author Comment

by:mohitgyl
ID: 33502197
I am doind it dyanimcally. The code is as follows:


substr(history_id,(locate('SC',HISTORY_ID )),((locate('xTS',history_id))-(locate('SC',HISTORY_ID)))) from ITMUSER.ncwipro where history_id like'%TS%'; .

0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 33502317
so there is probably a history_id value that does not contain 'SC' or does not contain 'xTS'
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

Author Comment

by:mohitgyl
ID: 33508198
As i said, if i try it as a select query in DB2 control centre, it is supoosed to give around 2 lacs rows in return and it gives the output , the output window is restricted to 100 rows by default if  i click fetch more rows button more rows are fetched out but after around 2200 rows it gives this error.
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 33512917
ok
that only means that the bad data comes around after 2200 rows
try to run this query

select
substr(history_id,(locate('SC',HISTORY_ID )),((locate('xTS',history_id))-(locate('SC',HISTORY_ID)))) from ITMUSER.ncwipro where history_id like'%SC%xTS%';
0
 

Author Closing Comment

by:mohitgyl
ID: 33767384
Thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question