Convert a packed value from a char field to numeric

[jborgh]

Hello
After browsing the internet, no results I found so here I am.
I have a database with a CHAR column/field. In that column I have very varied data, numeric and char values, but in some records I also have packed numeric values (in a CHAR field). This is the example of the first column of the image.
I need to convert that numeric packed value from that CHAR field into a real numeric data field, in my RPG pgm (SQL can be used). The original information cames from a TXT file that is uploaded into the DB.
(I cannot change the database column data type).
In the example, the second column is the real numeric value of the first column, I need to get to that value in the RPG to be able to write it in another DB.
Thanks in advance!

[Scott Klement]

Hello,

You say that the data in the first column are packed values, but they don't appear to be? If they were packed, the char field would be unreadable (unless you viewed it in hex or something, but that wouldn't look like the screenshot.)

Your CHAR_VALUE looks to me like two zoned decimal fields. The first one being a zoned 7 that is 7 digits, the second being a zoned 5 digit field. Since the 2nd field is only 5 long, the leftmost digits from your NUMERIC_VALUE columns have been discarded. So 50000.00 becomes 0 (since the leading 50 is dropped) and 1199.99 becomes 199.99 (since the leading 1 is dropped.)

Also, all of the fields are negative numbers. (including zero, despite that this doesn't really make sense.)

Basically, your data is not right... unless it has a different meaning? But, your data isn't right for packed or zoned data.

[Scott Klement]

Sorry. I take back the statement of "they're all negative" -- I misread the { characters. If they were } characters, they'd be negative. So this data is valid when viewed as two ZONED fields (but not packed.)

IBM i doesn't usually use { (which would be an implicit positive value) but mainframes do, and IBM i will understand them.

To convert the data, all you need is to put it into a data structure that has the zoned fields defined. (But this won't solve the problem of the leftmost digits being chopped off. There's no solution to that -- the data is simply not in the file.

**free

dcl-ds convert qualified;
extra_value zoned(7: 2);
numeric_value zoned(5: 2);
end-ds;

dcl-s char_value char(12);

char_value = '000000{0000{';
convert = char_value;
dsply %char(convert.numeric_value);

char_value = '000000{1999{';
convert = char_value;
dsply %char(convert.numeric_value);

char_value = '000000{5000{';
convert = char_value;
dsply %char(convert.numeric_value);

*inlr = *on;