Re: convert zoned to integer within SQL

On 10-Aug-2015 12:29 -0600, Alan Shore wrote:

On 10-Aug-2015 12:24 -0600, darren wrote:

On 10-Aug-2015 12:21 -0600, Alan Shore wrote:

Before I forget - we are on V5r4
Has anyone been able to convert a zoned field to integer within
SQL I know about and have used the api QXXZTOI, but I was hoping
someone had accomplished this in SQL
As always - all responses gratefully accepted

Maybe I'm missing something. Isn't it just:
SELECT INT(F1) from FILA

Thanks for your reply Darren - but that doesn't work on negatives
For example
00000000O where the last character is an alpha O not zero
This is a negative value

Apparently then, the desire is to effect conversion from a character-representation of Zoned Decimal *rather than* conversion from a Zoned Decimal data type as implied by the Subject. If all of the zoned decimal data is valid in the character field, and the negative values are all defined using the /preferred/ zone nibble of 0xD [hex digit D], and the character data has never gone through any CCSID translations [because 0xD0 is variant], then the following expression within the INT casting scalar of the below SELECT is an example of what would suffice [where the column named C9 has the zoned data stored as a CHAR(9); ideally, as FOR BIT DATA]:

create table qtemp/ztst (c9 char(9))
;
insert into qtemp/ztst values('00000000O')
;
select
int( ( left( C9, 8 ) * 10
+ right( hex( right( C9, 1 ) ), 1 )
)
* case
left( hex( right( C9, 1 ) ), 1 )
when 'D' then -1
else 1
end
)
from qtemp/ztst