Working with Packed Decimal

Most of the time, flat files feature simple text strings consisting of alphanumeric or signed decimal numbers. However, sometimes packed decimal representation is in the string. To get packed decimal data from a flat file in SQL, try the following statement:

```
SELECT o10seq, HEX(SUBSTR(o1dat1,8,6))
FROM greg/Cpf010 where o10rec=0

The values returned:

O10SEQ   HEX
728,449   00000000100D
728,450   00000050000F
728,451   00123456789F
728,452   00123456789D

```

To merely represent absolute values, just strip off the signed portion:

```
SELECT o10seq, substr(HEX(SUBSTR(o1dat1,8,6)),1,11)
FROM greg/Cpf010 where o10rec=0

The values returned:

O10SEQ   SUBSTR
728,449   00000000100
728,450   00000050000
728,451   00123456789
728,452   00123456789

```

If signed decimal numbers are the desired result, convert the signed portion:

```
SELECT o10seq,
DECIMAL(SUBSTR(HEX(SUBSTR(o1dat1,8,6)),1,11),11,0) * CASE
WHEN SUBSTR(HEX(SUBSTR(o1dat1,13,1)),2,1) ='F' THEN 1
ELSE -1 END
FROM greg/cpf010 where o10rec=0

The values returned:

O10SEQ              Numeric Expression
728,449    		            100-
728,450   	                 50,000
728,451  	            123,456,789
728,452         	    123,456,789-

```