Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum 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: 141

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: 447
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: 141

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: 141

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: 447
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: 2310
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: 141

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 OMIT first and last column of a pipe-... karthikb_itpro DFSORT/ICETOOL 1 Fri Nov 01, 2019 8:24 pm
No new posts Select based on a range from a differ... sergeyken SYNCSORT 2 Fri Aug 16, 2019 12:37 am
This topic is locked: you cannot edit posts or make replies. Select based on a range from a differ... vamsimul SYNCSORT 13 Tue Aug 13, 2019 12:36 pm
No new posts Convert Numeric to Packed decimal and... anand1204 DFSORT/ICETOOL 5 Thu Aug 08, 2019 3:53 am
No new posts Training on numeric fields data formats sergeyken SYNCSORT 12 Thu Aug 01, 2019 8:15 pm

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