Extracting Zoned and Packed Decimal Values from Character Fields

January 19, 2005 Hey, Ted

I have a physical file that includes a large alphanumeric field that is defined differently for many different record types. I am creating logical files for each of the record types and must redefine this large field in different ways for different record types. For some of the record types the field contains packed decimal values. How do I define a substringed field (one created with the SST keyword in DDS) as packed decimal?

–Mary

Your situation is not unusual, especially in shops that use packaged software. Since developers can’t foresee everything users may have to store, they sometimes include extra fields, sometimes called “user fields,” in which users can store anything they like. Querying these fields can be a challenge when numeric values are embedded in these fields.

Substringed fields are always character, so the logical file route is not going to work for you. I have an SQL alternative that you may want to consider. It’s as ugly as a mud fence, but it works.

Let’s assume a large character field, USRF01, that contains three numeric values. In RPG programs, you would define this field as a data structure in order to subdivide it.

D usrf01          ds
D   TypeCode              1      2s 0
D   Category              3      4p 0
D   Amount                5      8p 2

You’ll need to use SQL’s substring function to extract these fields.

substr(usrf01,1,2)
substr(usrf01,3,2)
substr(usrf01,5,4)

You might make some sense of the first field, which is in zoned decimal format, but the packed fields won’t be useful. You’ll need to do more in order to convert these fields into valid numeric values. What you have to do depends on two things–whether a field is in packed or zoned format, and whether a field can have a negative value or not.

Let’s look first at the zoned decimal field, TypeCode. Usually codes, account numbers, and anything else that is not a quantity or currency amount do not have negative values, so there is no need to allow for negatives in this case. Use the decimal function or integer function to convert the substring to a numeric value.

select decimal(substr(usrf01,1,2),2,0) as TypeCode
  from usrfldpf

select dec(substr(usrf01,1,2),2,0) as TypeCode
  from usrfldpf

select integer(substr(usrf01,1,2)) as TypeCode
  from usrfldpf

select int(substr(usrf01,1,2)) as TypeCode
  from usrfldpf

This solution is easy, but it wouldn’t work if TypeCode could be negative. Let me come back to negative zoned values after I show how to handle negative packed values.

Let’s look at the category subfield. Like TypeCode, it will not contain negative values, or at least it shouldn’t, so extracting its value is not so difficult. Convert the substring to its hexadecimal representation and extract all but the last hex digit, which indicates the sign. For example, a category code of 123 is hexadecimal 123F. Once you’ve extracted all but the last hex digit, use either decimal or integer to convert to numeric.

select dec(substr(hex(substr(usrf01,3,2)),1,3),3,0)
           as Category
  from usrfldpf

Here the inner substring function extracts two bytes beginning with the third byte. The hex function converts it to four characters that indicate the hex digits. The outer substring function extracts the first three characters, which the decimal function converts to numeric format.

Now take a look at the Amount subfield. There are two complications to consider–the field can be negative, and it contains a decimal point.

As with the category, you’ll need to work with the hex digits of this field in order to convert to numeric. The last hex digit is the sign, so ignore it for now. The following extracts the digits of the number, inserting a decimal point in the proper position.

select dec(
         dec(substr(hex(substr(usrf01,5,4)),1,5)||'.'||
             substr(hex(substr(usrf01,5,4)),6,2),
         7,2),
       7,2) as Price
  from usrfldpf

To make the number positive or negative, check the last hex digit. If the digit is D, the number is negative. Otherwise, assume it’s positive. (This is not a valid assumption on all systems, but it works fine under DB2/400.) If you find a D, multiply the number you extracted by negative one. Otherwise, multiply by 1.

select dec(
         dec(substr(hex(substr(usrf01,5,4)),1,5)||'.'||
             substr(hex(substr(usrf01,5,4)),6,2),
         7,2) *
         (case when substr(hex(substr(usrf01,5,4)),8,1) = 'D'
                 then -1 else 1 end),
       7,2) as Price
  from usrfldpf

The only loose end to tie up is what to do about zoned fields that can be negative. We’ll return to the TypeCode field for an example, even though such a field is unlikely to have a negative value.

As with packed fields, you must convert the zoned field to hexadecimal and extract all the digits. You will need to extract the second digit of each pair, since the hex value of a number is either a D or F followed by the digit itself. That is, the value 250 is represented in hex as F2F5F0. The first hex digit of the last pair contains the sign of F or D.

Since TypeCode is two digits, the following calculation extracts its value. The second digit of each hex pair is concatenated to form a string, which is converted to numeric with the decimal function. This converted value is multiplied by positive 1 or negative 1, depending on the first hex digit of the last pair, in order to apply the sign to the number.

select dec(
        dec(substr(hex(usrf01),2,1)||substr(hex(usrf01),4,1),2,0) *
        (case when substr(hex(substr(usrf01,1,2)),3,1) = 'D'
              then -1 else 1 end)
       ,2,0) as TypeCode
  from usrfldpf

I told you it wouldn’t be pretty. In a perfect world, we wouldn’t have to resort to such gymnastics. If my more than 23 years in this industry has taught me anything, it’s that we don’t live in a perfect world.

I have overlooked the obvious before. I hope I am doing so again. Maybe someone out there can show all of us a better solution to an all-too-common problem.

–Ted

Click here to contact Ted Holt by e-mail.