Solved

SQL0138N

Posted on 2010-08-23
6
1,220 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

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…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 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