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! :/
Now, if that doesnt 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 you 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 crappy 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
- TRIMMED – just in case it was stored as “97 ” then it will be right adjusted ” 97″ (note the movement of the ‘blank’)
- DECIMALISED – so the treated as a numeric… for example 097
- 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’