Need to unpack fields using SQL (if possible)

[MelissaG]

We have an obnoxious old file where the data I need is in one field. I'm trying to get the information using SQL so I can pick some records to use in a test without reading through thousands of records. I've used SUBSTR in my query to pull out the fields I need, but I don't quite know if I can unpack the packed fields.

I found some references to UNPACK using Google - and maybe that's the answer, but I still get bogged down in what I can do and what I can't.

I'm using iAccess to run this statement. I am pretty sure that my problem is that I don't know the proper syntax - at least, that's what I hope.

Does anyone have a simple example?

something like this -- only QTYORD is packed; the rest are character fields.

Select   SUBSTR(AYCBTX,1,6) AS ACKNO,             
        SUBSTR(AYCBTX,7,3) AS SUFFIX,            
        SUBSTR(AYCBTX,10,3) AS LINENO,           
        SUBSTR(AYCBTX,13,9) AS ACKKEY,           
        SUBSTR(AYCBTX,21,15) AS PRODUCT,         
        SUBSTR(AYCBTX,36,5)) AS QTYORD 

I hope everyone is staying safe.

[inigo.redin]

Hi.

The only way I know to unpack packed fields is using SUBSTR combined with HEX.

Example:
SUBSTR(HEX(SUBSTR(AYCBTX, 36, 5)), 1, 09) QTYORD


Why 09 in the SUBSTR? because your field has 5 bytes. That means 10 digits. But the last one is used to indicate if the number is positive or negative.

Be careful, this is valid just for packed numbers.

I usually work with VIEWS to make this easier.

[JonBoy]

Wouldn't the simplest thing be to create a logical/view over the data specifying the substrings and giving them the correct data type?

[MelissaG]

Well, d'oh ! Yeah, sometimes I just don't see the forest for the trees. Thank you.