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-