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.


This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].