Hey, Ted:

I was wondering if there is any way of creating a join file with some flexibility? I will try my best to provide an example:

I have one file that contains several fields, one of which is a 998-byte string of data. I have another file that contains the mapping (start position & field length) for the string. Part of each of the files is a part number that I can use to join the files. Each part may be mapped differently. The number of fields mapped from part to part may be different.

I would like to join the two files and create mapped fields that use the mapping coordinates to extract appropriate substrings from the 998-byte field. The trick is that mapping coordinates may change from part to part.

Here's an example for two parts. Two values are to be extracted from part number 1, while three values are to be extracted from part number 2.

String File

Part# String (only showing 10 chars for my example)
1 1234567890
2 0987654321

Mapping file Part# Field Start Field length Mapped Value 1 1 3 123
1 4 7 4567890
2 1 4 0987
2 5 1 6
2 6 5 54321

Any suggestions, Ted?

That's a very interesting and intriguing question. I am a big fan of softcoding (placing information into files, rather than in source code) and am always interesting in seeing how someone somewhere softcodes applications. When information is softcoded, it is easily changed with a file maintenance program, which means a programmer is not required to modify and recompile a program.

The challenge you've presented me is easily handled with SQL's substring function. Substring accepts three arguments--the string from which data is to be extracted, the position at which to begin extracting, and the number of characters to extract. Users of SQL typically use literal values for the second and third arguments because we usually know in advance how many characters we need and where that string of characters begins. However, the second and third arguments may be resolved at run-time. We tend to forget this, since we rarely use it.

Here's some SQL that builds your two files and loads the values in your example.

create table stringf (
PartNbr dec(5,0),
PartString char (10),
primary key (PartNbr))

create table mappingf (
PartNbr dec(5,0),
FieldStart dec(3,0),
FieldLen dec(3,0))

insert into stringf values (1,'1234567890')
insert into stringf values (2,'0987654321')
insert into mappingf values (1, 1, 3)
insert into mappingf values (1, 4, 7)
insert into mappingf values (2, 1, 4)
insert into mappingf values (2, 5, 1)
insert into mappingf values (2, 6, 5)

Here's the SQL statement that extracts the mapped values you requested. Because I do not understand the purpose of this mapped field, I called it XYZ. I'm sure you can come up with something more descriptive.

select a.PartNbr, FieldStart,
FieldLen, PartString,
substr(PartString, Int(FieldStart),
Int(FieldLen)) as xyz
from stringf as a
inner join mappingf as b
on a.PartNbr = b.PartNbr

One way to make it easier to work with this definition is to store it as a view. A view is similar to a logical file and is created from a select statement. The following SQL statement creates a view over the stringf and mappingf tables. Using a view saves you from having to copy the join criteria.

Create view partmap as
select a.PartNbr, FieldStart,
FieldLen, PartString,
substr(PartString, Int(FieldStart),
Int(FieldLen)) as xyz
from stringf as a
inner join mappingf as b
on a.PartNbr = b.PartNbr

Query/400's substring function can also accept expressions in the starting and length arguments. Here's the same calculation done with Query:

Field Expression
XYZ substr(PartString,FieldStart,
FieldLen)

-- Ted

SPONSORED BY VISION SOLUTIONS Access Your Data. Anytime. Anywhere. http://www.visionsolutions.com

Hey, Ted:

I recently started using the Personal Communications emulator that comes with Client Access Express. One feature that bothers me is that the backspace key doesn't work like the dumb-terminal counterpart. Backspace rubs out the character in front of the cursor. I would prefer the cursor to move to the left without destroying what's already there. Can this be changed, perhaps through the keyboard customization feature?

Sure. It's easy to make the keys do whatever you'd like. Choose the Edit drop-down menu from the menu bar at the top of the Personal Communications window. From there, choose Preferences, then Keyboard. This opens up the Keyboard Setup dialog box. (The Preferences dialog may be on the Assist drop-down menu on some systems.)

The Keyboard Setup dialog box is open. For Current keyboard, select "user-defined", then click on the Customize button. The keyboard map appears on the screen. The top of the display shows the layout of the keyboard.

Now you're ready to remap that backspace key. Click on the image of the backspace key. Move the mouse pointer to the bottom left of the screen and look for the Function drop-down box. Click on the down arrow on the right end of that box and choose Cursor left. Move the mouse pointer to the right, to the area marked Change Current Actions for Selected Key. Since you want to change the function of the backspace key when it is pressed alone, i.e., not in combination with other keys, click on the button to the left of the word Base. (This button has a hyphen and right angle bracket on it, making a crude arrow.) The value in the white box to the right of the word Base should change to [left].

Now you need to save the change. Pull down the File menu and select either Save or Save As, depending on whether you want to give the keyboard customization file a name of your own. I just use the default value of AS400.KMP. Then choose Exit from the File menu to return to the Keyboard Setup dialog box. Click on OK and you're back to the emulation section with a non-destructive backspace key.

Now that you know how to customize a key, put this feature to work. Here are two other ideas that I've found helpful.

1. Map the Ruler function to Alt-R. Choose the image of the R key from the keyboard map. Select Rule in the Function drop-down box, and click on the button to the left of Alt in the Change Current Actions for the Selected Key section. Control-Home is already mapped to rule, but I find it clunky to use and hard to remember.

2. Map Control-C and Control-V to the copy and insert functions. This makes them work they way they do in Microsoft programs like Word, Excel, and Access. The values to select in the Function drop-down box are Edit Copy and Edit Paste. You'll need to click on the crude arrow button to the left of Ctrl in the bottom right corner of the display. These functions are already mapped to Control-Insert and Control-Shift-Insert, but those key assignments are even harder for me to use and remember than the aforementioned Control-Home assignment.

3. Map Alt-Left Arrow to jump previous active and Alt-Right Arrow to jump next active. This allows you to switch between active sessions using the same key combinations that Code/400 uses to switch between files.

I encourage you to spend some time reading the help text in Personal Communications. There is quite a bit of information about keyboard customization and you may get some other good ideas. In addition to simple keyboard mappings, you will find information that describes how to create macros and attach them to keyboard shortcuts.

If any readers have favorite keyboard mappings, send them to me and I'll present them in an upcoming issue of the Midrange Guru.

-- Ted  

TigerTools offers Fast400 to slash interactive response times on AS400 and iSeries servers. Fast400 helps AS400 and iSeries users get better interactive response time WITHOUT having to upgrade the server hardware. This software solution allows interactive users to get more throughput on their AS400 and iSeries server. Fast400 uniquely "tunes" each interactive job on the system. Depending on the AS/400 and iSeries Interactive Features (CPW) installed on your machine, some users can see an increase of 1000% interactive throughput. To learn more about CPW and how Fast400 can affect your server, you can visit http://www.barsaconsulting.com/barsa-tips.htm Fast400 is available to evaluate in your own environment. Fast400 is completely available online. In a matter of minutes, you can download the documentation, the software, and a DEMO license key from http://www.tigertools.com/default.asp Pricing starts from $1,000 USD. Pricing depends on model and feature codes. Quantity discounts are available. For more information, you can email us at sales@tigertools.com or call 800-266-7240 x1700.

One of the great things about the OS/400 community is that it is indeed a community. We may be all working from our cubicles, but we are all connected and trying to figure out how to best employ the computer technology at our disposal. There are more than a few ways to skin any cat, and if you have a clever and unique answer to a problem that one of our Midrange Gurus has solved, we'd love to hear from you. This newsletter is an open dialog, and we value your input as well as your readership.

It goes without saying--but we'll say it anyway--that your hard technical questions pertaining to real world problems are equally valuable as a foundation for this newsletter as are your programming insights. We hope you find all the editions of Midrange Guru valuable, and we are going to work hard to make sure that they are.

If you have a tough problem, our gurus can probably help. Their mailboxes are always open.

* Email Ted Holt at tholt@itjungle.com