Thanks Chuck
You definitely described it MUCH better than I did
AND
You nailed it on the head
Now I just need to apply what you gave me
MUCH appreciated
Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Monday, August 10, 2015 4:16 PM
To: midrange-l@xxxxxxxxxxxx
Subject: 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
--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
