text data to packed

75 pts.
Tags:
AS/400 SQL
SQL
i have comp-3 data in a text field. i need to extract the info and convert it back to numerics to download to a pc. i want to use sql ...

Software/Hardware used:
as/400- client

Answer Wiki

Thanks. We'll let you know when a new response is added.

You could alter your cobol program to write the field as packed or as text in a text field.
You could write a cobol program to read the data and write to a new file with a packed field
You could copy this file to another file with a packed field – cpyf with *NOFMT
Phil

===========================================================

Assume column MIXED is CHAR(10) with positions 1-3 simple characters and positions 4-10 as PACKED(13,0). That is, the first three bytes can be separated with SUBSTR(MIXED,1,3) and the last seven bytes as SUBSTR(MIXED,4,7). The packed bytes can be exposed with HEX(SUBSTR(MIXED,4,7)). If the hex characters are x’0000000000001F’, the decimal value is +1. The first 13 characters are all numeric, so the DEC() function can be used:

<pre>
dec(substr(hex(substr(MIXED,4,7)),1,13),13,0)
</pre>

If there are decimal positions, the 13 hex characters can be separated with two SUBSTR() functions and CONCATed around a decimal point before processing with the outer DEC(). If the 14th character indicates a negative value, the expression can be multiplied by -1.

Create a VIEW to parse the compound field and to expose the sub-values. Use the view for sending values to a PC.

Tom

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following