## Sorting alphanumeric field with hexadecimal values

135 pts.
Tags:
AS/400
CL
RPG
We have a task where we have to convert key numeric field (8,0) to alphanumeric field (8A) with hexadecimal values ('00000000' - 'FFFFFFFF'). The problem in sorting with alphanumeric is the alphabet is less than the numeric value (A-Z < 0-9). Is there a way, by LF or any AS400 utility, that can sort hexadecimal data in alphanumeric field? Thanks!

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

Yes,By assuming Keyword ALTSEQ(QSYS/QASCII) in LF can only solve the sort sequence problem,How to value the KEYID,I think Tom’s say is a right way.
So others about the sequence like these:
1: The table which you use is decided by your pratical applications.
This table QASCII is tranfer the EBCDIC code to ASCII code,because the char&numeric sort sequence is reversely,so the table QASCII was choosed.
2: If some of the keylist don’t want to change the sort sequnce, the keyword NOALTSEQ(a key field-level keyword) is assumed to the specific fields.
Jackli

## Discuss This Question: 14 Replies

Thanks. We'll let you know when a new response is added.
• You numeric values will always be after the alpha values. I do not understand the converting that you describe. If you have a 8 position signed numeric field, that 1st position will always be a digit. If the field is packed numeric that is different.
report
• let me see if I understand the question: you want to convert a numeric value to hexadecimal (15 => 0000000F), then store that value in a 8A field but you still want it to sort as if it was the value 15 and not a character string of '0000000F'
report
1. The database definition of the current "numeric field",
2. The database definition of the new "alphanumeric field"
3. A few examples of values that exist now, and
4. The new converted values that you want to create.
It might also be useful if you explain why the collating sequence is a problem. What is it a problem for? Do you want to create programming that doesn't have a problem with the collating sequence? Tom
report
• Hi everyone! I apologize for the late reply. We have project where the client needs to increase the limit of key record number (8,0) in a file. The problem with the numeric field (8,0). It can only handle maximum of 99999999 records. They don't purge these records so they are having problems now the number of records is reaching its limit. A maintenance program increments the record number every time a new record is created. First we suggested increasing the length of the numeric field (10,0 or bigger). But our project manager decided against it because a huge number of files and programs will be affected and we are on a very tight schedule. So we suggested to change the numeric field to a alphanumeric field which would contain hexadecimal values. This would greatly increase the number of records (00000000 - FFFFFFFF). In reports and display/maintenance programs, these records should be arranged in its hexadecimal manner. (00000000 -> FFFFFFFF). With the current sequencing of the LF, character values are less than the numeric values (A-Z < 0-9). Example ABCD1234 FFFFFFFF 00000000 1234ABCD 99999999 Where it should be displayed/printed as 00000000 1234ABCD 99999999 ABCD1234 FFFFFFFF Is there a way where this sorting can be accomplished? Many thanks!
report
• Is there a way where this sorting can be accomplished? Yes, but two questions still need to be answered:
• The database definition of the current “numeric field”,
• The database definition of the new “alphanumeric field”
The task is clearly complicated "because a huge number of files and programs will be affected". Without knowing exactly what is being manipulated, many can go wrong. Tom
report
• 00000000 1234ABCD 99999999 ABCD1234 FFFFFFFF The way you describe it, you're not actually using "hex" values. You're just using characters instead of numerics, and you're allowing new characters to be used. I think that has introduced some confusion. (It did for me.) But that's okay. It actually might make it much easier. Tom
report
• The database definition of the numeric field is 8S0 (current situation) The database definition of the alphanumeric field is 8A (suggested change) I hope I answered your question. Yes. A large number of display/printer files, programs will be affected by this change. But the changes will be easier to implement. Programmers will just have to change the data format from 8,0 to 8A in DSPF/PRTF/PF/PGM, modify numeric operations to alphanumeric operations in PGMS, and add validations for the record field (8A with hex value). Sorry if you got confused with my explanation. Thanks again!
report
• ...the numeric field is 8S0... Is that from DDS? Or is it a RPG program definition? If it's actual DDS, then we can guess the database definition without needing to see it. (You can use DSPFFD over the database file to see the database definition of a field. DDS should map to a database definition, but RPG may have misleading differences.) If the database field is actually ZONED 8 0, then you might easily be able to alter it to be CHAR 8. And by assigning your desired alternate collating sequence to it, you should be able to sort it as if it is a character representation of hex digits. Tom
report
• Yes, you are correct. All objects (PF, DSPF, PRTF, CL/LE, and RPG/LE) have the data defined as zoned 8 0. And the developers will change it to 8 alphanumeric. The question is, how to sort the the 8 alphanumeric in "hexadecimal" sorting using LF or other AS400 operations? Or can this be done in RPG/LE only? Thanks!
report
• You can do like this: Create(or change） a LF by add file level keyword: ALTSEQ(QSYS/QASCII),so the LF will sort by the all key field ascii code sequence not by the ebcdic code sequence.This keyword only change the key field sequence but not change the field data.I think the table QASCII must be existed,If no,you can CRTTBL to create a user defined table. jackli