Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
mohitgyl

New User

Joined: 24 Aug 2010
Posts: 3
Location: Bangalore

 Posted: Tue Aug 24, 2010 4:52 pm    Post subject: 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

dbzTHEdinosauer

Global Moderator

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

 Posted: Tue Aug 24, 2010 5:37 pm    Post subject: **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.
mohitgyl

New User

Joined: 24 Aug 2010
Posts: 3
Location: Bangalore

 Posted: Tue Aug 24, 2010 5:40 pm    Post subject: 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%';
dbzTHEdinosauer

Global Moderator

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

 Posted: Tue Aug 24, 2010 5:53 pm    Post subject: 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.
mohitgyl

New User

Joined: 24 Aug 2010
Posts: 3
Location: Bangalore

 Posted: Tue Aug 24, 2010 6:36 pm    Post subject: 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.
dbzTHEdinosauer

Global Moderator

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

Posted: Tue Aug 24, 2010 6:49 pm    Post subject:

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
GuyC

Senior Member

Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

 Posted: Tue Aug 24, 2010 7:01 pm    Post subject: easily tested : where locate('SC',HISTORY_ID ) < 1 or (locate('xTS',history_id)-locate('SC',HISTORY_ID) < 1)
 All times are GMT + 6 Hours
 Page 1 of 1
 Search our Forum:

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us