Left Adjust Alpha into Numeric using RPG Language

Left adjusting Alpha Values into Numeric Fields with RPGLE

So, I was writing a little code snippet for a client after being told that all warehouse codes must be treated as 3 numeric character integers ranging from 1-999 and soon saw that the warehouse value was an alphabetic field? Huh?

A little more digging around, and I soon found a problem in their database design: They have a database storing warehouse codes, but in some tables they are stored as two character alphameric values, sometimes as three character alpha’s and other times as 3 character numerics! #aaaargh

Now, if that doesn’t set off all kinds of alarm bells in your brain, then you probably want to stop reading and go and make a nice cup of tea and watch a reality show on the Discovery channel.

But, if you are a techie and just scratched your head and thought, “WTF? Who designed that database then?” the simple answer is NOT ME.

Obviously, it was designed by a maniac.

Sadly, I’m the programmer who has to work with it, and it’s established in the business, so us poor programmers just have to figure out how to best work with it.

Anyway, before I spend too much time moaning about lazy programmers and their sloppy database design, this is how I got around it.

I used a neat little combination of RPGLE %BIF’s (Built In Functions) to take the value of any alphanumeric character and left adjust it into a numeric, padding leading zeros if necessary… like this:

ALPHAFIELD = %Editc ( %Dec(%Trim(ALPHAWHSE):3:0):'X' );

So, lets say ALPHAWHSE = ’97 ‘ that is NINE SEVEN BLANK then it will be

1. TRIMMED – just in case it was stored as “97 ” then it will be right adjusted ” 97″ (note the movement of the ‘blank’)
2. DECIMALISED – so the treated as a numeric… for example 097
3. EDITTED – now using EDITC(‘X’) which preloads the value with leading ZEROS.

The nice thing about this is that if the value is either “97_” or “_97” is passed in then it will be returned in ALPHAFIELD = ‘097’

NOTE: This code will explode if the field ALPHAWHSE holds anything other than numbers. Use a TESTN to check it? Or a MONITOR group? There are lots of techniques to validate it, before trying the conversion.

NOTE: You can use a variant of this technique to load a ALPHA VALUE with blanks into a numeric field:

NUMERICFIELD = %Dec(%Trim(ALPHAWHSE):3:0);

This says “trim the leading blanks out of the alpha value” ie: left adjust it” and treat is as a 3,0 numeric