Solved

SQL0138N

Posted on 2010-08-23
6
1,209 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Last 4 fiscal years for a given date 8 212
Continuously query a database in Java 10 143
finding tables 2 25
Monitor SQL Insert 8 45
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…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

896 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now