Re: Converting Character Zoned Data to Numeric in SQL

[CRPence <CRPbottle@xxxxxxxxx>]

  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.