[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1258
  • Last Modified:

SQL0138N

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
mohitgyl
Asked:
mohitgyl
  • 3
  • 3
1 Solution
 
momi_sabagCommented:
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
 
mohitgylAuthor Commented:
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
 
momi_sabagCommented:
so there is probably a history_id value that does not contain 'SC' or does not contain 'xTS'
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
mohitgylAuthor Commented:
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
 
momi_sabagCommented:
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
 
mohitgylAuthor Commented:
Thanks
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now