Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Select numeric portion from CHAR datatype column

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 116

PostPosted: Sat Aug 19, 2017 1:51 am    Post subject: Select numeric portion from CHAR datatype column
Reply with quote

Hi Friends,

I am trying select a numeric portion from char field which is of length 10 .
I am trying to use cast and substr together but it is giving error .
Can you please help and advise how we can achieve the numeric portion as integer from SQL .

OPER is char (10) and it hold value like 'OPS01234 ' , need to retreive 01234 as int with length of 5 bytes.

Code:

SELECT CAST(SUBSTR(OPER,4,5) AS INTEGER)
FROM TEST_TABLE

got SQL error =  -420 "Value of string argument was not acceptable to
sysibm.integer function


can any one help me to overcome this error.
Back to top
View user's profile Send private message

RahulG31

Active User


Joined: 20 Dec 2014
Posts: 397
Location: USA

PostPosted: Sat Aug 19, 2017 3:08 am    Post subject: Reply to: Select numeric portion from CHAR datatype column
Reply with quote

If the value of SUBSTR(OPER,4,5) is numeric, then you'll Not get any error.

But, if the SUBSTR(OPER,4,5) contains any non-numeric value then you'll get sqlcode -420.

So, you may be getting a -420 for a value that is Not OPS01234.

Check all the values you are getting with SUBSTR(OPER,4,5) i.e. don't CAST yet and see the values to find any non-numeric data.

.
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 116

PostPosted: Mon Aug 21, 2017 9:49 pm    Post subject: Re: Reply to: Select numeric portion from CHAR datatype column
Reply with quote

RahulG31 wrote:
If the value of SUBSTR(OPER,4,5) is numeric, then you'll Not get any error.

But, if the SUBSTR(OPER,4,5) contains any non-numeric value then you'll get sqlcode -420.

So, you may be getting a -420 for a value that is Not OPS01234.

Check all the values you are getting with SUBSTR(OPER,4,5) i.e. don't CAST yet and see the values to find any non-numeric data.

.


Sure Rahul , i will check in this.
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 116

PostPosted: Tue Aug 22, 2017 2:42 am    Post subject:
Reply with quote

Hi Rahul,
It works good for me .Thanks icon_smile.gif
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 397
Location: USA

PostPosted: Tue Aug 22, 2017 2:56 am    Post subject:
Reply with quote

balaji81_k wrote:
Hi Rahul,
It works good for me .Thanks :)

So, that means that you were able to find the non-numeric data in SUBSTR(OPER,4,5) and that was causing sqlcode -420, right?

.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1788
Location: NY,USA

PostPosted: Tue Aug 22, 2017 3:18 am    Post subject:
Reply with quote

Why Hardcode? if the position changes in future then what?
Code:
select
replace(RTRIM(TRANSLATE(upper('OPS01234 '), ' ', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ','' )
END from sysibm.sysdummy1
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 116

PostPosted: Wed Aug 23, 2017 9:31 pm    Post subject:
Reply with quote

Rohit Umarjikar wrote:
Why Hardcode? if the position changes in future then what?
Code:
select
replace(RTRIM(TRANSLATE(upper('OPS01234 '), ' ', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ','' )
END from sysibm.sysdummy1


Many thanks Rohit for the suggestion.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Remove leading spaces from numeric field rexx77 SYNCSORT 6 Wed Sep 06, 2017 2:15 am
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts Return a Non Numeric value from REXX Learncoholic CLIST & REXX 3 Fri Sep 01, 2017 8:33 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us