Logo: TUG TORONTO USERS GROUP for Midrange Systems
e -server magazine

September 1997: Volume 13, Number 1

Tips from Jackie

CUSTNO or 'Customer Number'

By Jackie Jansen

hree years ago IBM delivered V3R1. One feature often overlooked with V3R1 was support for long field names. Yes, I know many of you are RPG programmers and think that you can't use long field names and therefore why bother. But you should be interested. The AS/400 allows a field to have two different field names concurrently - a short field name and a long field name. RPG will automatically pick up the short field name. What is really nice is that all your external tools such as MS Excel, Crystal Reports, Impromptu, etc. will all pick up the long field name.

I can have a field that RPG recognizes as CUSTNO and Cognos Impromptu recognizes as Customer Number. Anyone, with V3R1 or beyond, has this capability. If you are using DDS to create your files, you need to specify the ALIAS keyword. This gives you the long field name in upper case only. If you are using SQL, you can specify the name in quotes and get upper and lower case names such as "Customer_Number". The AS/400 will accept blanks in long field names.

Here are a few recommendations based on experience. Don't put blanks in your field names. Some PC programs can't handle this. Underscores work great. Similarly I have found one or two PC programs that had problems with upper and lower case field names so be careful. Upper and lower case names do look very nice. Test out your main PC application before you add the ALIAS to all your files. Cognos Impromptu has a very interesting feature. It automatically replaces your underscores with blanks and attempts to translate your field name to upper and lower case. If your AS/400 field name is CUSTOMER_NUMBER, Impromptu will display this to the end user as Customer Number.

Operations Navigator with V3R7 is another vehicle for entering field names with upper and lower case. When you use Operations Navigator, you must surround your field name with single quotes. Currently, those quotes will show in most PC packages. For example, your end users many see 'Customer_Number'. If you use all upper case characters, your end user would see CUSTOMER_NUMBER.

If you are on a release that fully supports the new functions in CHGPF (see a previous Tips from Jackie on TUG's home page), you have a super way of easily adding new, long field names to your existing files. Edit your original DDS for the file, adding in the function ALIAS(NEW_FIELD_NAME) for each field. Execute the CHGPF command referencing this DDS source. Your file will retain all its records and your program accessing this file will NOT level check. Your RPG programs will run just as before. You will now be able to nicely use a program like MS Excel to access your AS/400. When you request external data with MS Excel and choose the AS/400 ODBC driver, you will be able to select your file and the field names displayed will be very readable.

Happy data accessing! T < G

Jackie Jansen is an AS/400 Specialist and Consulting SE, providing national technical and marketing support for the AS/400 in Canada. Jackie frequently speaks at AS/400 Technical Conferences and User Group meetings.