View previous topic :: View next topic
|
Author |
Message |
mohitgyl
New User
Joined: 24 Aug 2010 Posts: 3 Location: Bangalore
|
|
|
|
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 |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
**Sigh**
what makes you think that providing the sql error explanation in you post
will help us to determine your problem?
what does your substr look like?
obviously you are not using literals, but instead calculated values
for the start parameter and the length parameter.
without knowing what your substr looks like,
we can not help you. |
|
Back to top |
|
|
mohitgyl
New User
Joined: 24 Aug 2010 Posts: 3 Location: Bangalore
|
|
|
|
It looks like
substr(history_id,(locate('SC',HISTORY_ID )),((locate('xTS',history_id))-(locate('SC',HISTORY_ID)))) from ITMUSER.ncwipro where history_id like'%TS%'; |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
well,
you are assuming that 'SC' and 'xTS' will always be contained
in the column HISTORY_ID.
obviously, it is not. your where clause only looks for 'TS' which means values other than 'xTS' would qualifiy. |
|
Back to top |
|
|
mohitgyl
New User
Joined: 24 Aug 2010 Posts: 3 Location: Bangalore
|
|
|
|
Yes, Where clause will look for TS values only but it is kind of data that if TS is coming then SC will always come along with it. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
again,
because you are looking for 'TS',
Code: |
where history_id like'%TS%' |
values other than 'xTS' will qualifiy.
That means this part of your substr
Code: |
(locate('xTS',history_id) |
will potentially result = 0
and then subtracting any value
Code: |
-(locate('SC',HISTORY_ID) |
will result in a length < 0
which is resulting in a -138 |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
easily tested :
where locate('SC',HISTORY_ID ) < 1
or (locate('xTS',history_id)-locate('SC',HISTORY_ID) < 1) |
|
Back to top |
|
|
|