Re: Converting Character Zoned Data to Numeric in SQL

As noted in another post, the EBCDIC 0xF0 to 0xF9 are merely the /preferred positive/ on the i, but other values are allowed. So for instance if the /flat file/ data had been generated somewhere else, e.g. on a system z, the positive data\values might have the 0xC0 to 0xC9 as the sign digit, instead of the 0xF0 to 0xF9. Thus the quoted solution would return negative values, for what are actually supposed to be positive values.

I am out west and so unsure, unlikely actually, if I will be back in Rocs anytime soon.

Regards, Chuck

Vern Hamberg wrote:

Had forgot about that - it does the trick and beautifully!
Here's the result -

SELECT
case
when substr(chr1,length(chr1),1) between '0' and '9' then 1
else -1
end *
zoned(
substr(chr1,1,length(chr1)-1) concat
lor(x'F0', substr(chr1,length(chr1),1))
)
FROM vern/chrtozon

The CASE construct determines the sign.

The LOR forces the final character to a positive number.

So it's the sign times the absolute value - which would not be
usable here, I suppose.

VOILA!

BTW, I'll be in Rochester mid-November - maybe can get together.