IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SQL0138N


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
mohitgyl

New User


Joined: 24 Aug 2010
Posts: 3
Location: Bangalore

PostPosted: Tue Aug 24, 2010 4:52 pm
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Aug 24, 2010 5:37 pm
Reply with quote

**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
View user's profile Send private message
mohitgyl

New User


Joined: 24 Aug 2010
Posts: 3
Location: Bangalore

PostPosted: Tue Aug 24, 2010 5:40 pm
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Aug 24, 2010 5:53 pm
Reply with quote

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
View user's profile Send private message
mohitgyl

New User


Joined: 24 Aug 2010
Posts: 3
Location: Bangalore

PostPosted: Tue Aug 24, 2010 6:36 pm
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Aug 24, 2010 6:49 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Aug 24, 2010 7:01 pm
Reply with quote

easily tested :

where locate('SC',HISTORY_ID ) < 1
or (locate('xTS',history_id)-locate('SC',HISTORY_ID) < 1)
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Search our Forums:

Back to Top