Re: Converting Character Zoned Data to Numeric in SQL

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

I wondered about the various possible nybbles that can be there for 
negative - did not remember or know of those for positive - are there 
more? Various references on the 'Net suggest there are only C & F for 
positive, D for negative. Adding another range is easy enough, it it'll 
take care of the problem.

Thx
Vern

CRPence wrote:
>    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.
> >
> >