|
View previous topic :: View next topic
|
| Author |
Message |
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
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 |
|
 |
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
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 |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
| 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 |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
Hi Rahul,
It works good for me .Thanks  |
|
| Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
| 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
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 |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
| 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 |
|
|
|
|
