Re: Converting Character Zoned Data to Numeric in SQL

Jerry Adams wrote:

Luis Rodriguez wrote:

<snip> BTW, in our system (V5R3) , a negative ZONED field
registers as N. </snip>

The displayed value (DSPPFM) of a signed field is a combination
of the zone and digit portions of the byte. The result, ergo,
will depend upon the value of the digit portion since, for a
negative value, the zone is always a 'D'. On a display file
panel, if one does not leave room for the sign on the right, the
character value (J-R, }) will be displayed; otherwise, the
numeric value will be displayed with the sign at the right.

I started out on a Burroughs system (don't ask) years ago. The
sign for numeric fields was LEFT justified (i.e., in the first
byte whether packed or unpacked). When we converted to a S/3, we
had to write a program to convert the sign to the RIGHT most
byte.

The /preferred negative/ sign nibble is 0xD. As merely the "preferred" value, that precludes use of "always" to describe generally, what data may be seen for the sign byte; i.e. unless the database physical file is an SQL TABLE, any of the values 0xA to 0xF are valid sign nibbles for BCD [Binary Coded Decimal] zoned portion of a zone decimal sign digit.

Check out the following scenario which includes many of those values [4 to 7 dropped; "negative zero" included], where character values such as 's', 't', and 'Ñ' are shown to be valid; the CPYF would fail if the data was not valid:

create table z2dta (z2 character(2) for bit data)
;
create table z2 (z2 numeric (2, 0))
;
insert into z2dta values
(x'F0A0'),(x'F1A1'),(x'F2A2'),(x'F3A3') ,(x'F8A8'),(x'F9A9')
,(x'F0B0'),(x'F1B1'),(x'F2B2'),(x'F3B3') ,(x'F8B8'),(x'F9B9')
,(x'F0C0'),(x'F1C1'),(x'F2C2'),(x'F3C3') ,(x'F8C8'),(x'F9C9')
,(x'F0D0'),(x'F1D1'),(x'F2D2'),(x'F3D3') ,(x'F8D8'),(x'F9D9')
,(x'F0E0'),(x'F1E1'),(x'F2E2'),(x'F3E3') ,(x'F8E8'),(x'F9E9')
,(x'F0F0'),(x'F1F1'),(x'F2F2'),(x'F3F3') ,(x'F8F8'),(x'F9F9')
;
cpyf z2dta z2 frommbr(*first) tombr(*first)
mbropt(*replace) fmtopt(*nochk)

Regards, Chuck