Solved

SQL0138N

Posted on 2010-08-23
6
1,205 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 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

13 Experts available now in Live!

Get 1:1 Help Now