Using reference fields

When I first started programming on the AS400 the biggest improvement I encountered, compared to the System/36, was the external database. No longer did I have to have file specifications in my RPG programs, and I now had the ability to define fields by referencing another. Over the years I have used the referencing ability whenever I can had to define fields in files or variables in my RPG programs. By using reference fields makes database changes easier as a field's definition is changed in one place, then by recompiling all of the objects with the referencing, the new objects all have the new definition of the field in them.

In the past fortnight I have been asked several times about using reference fields, and in my experience there are many more who would like to ask the same question but feel intimated to do so. Therefore, I am writing this post to give examples of how I use them and why I do things the way I do.

All well-built databases have a data dictionary that defines the types of all the fields, or columns, within it. On AS400 and IBM i many of us achieved something similar using a reference file. This file contains the definition of every type of field that could be contained in any of the other files in the application.

01 A R REFFILER 02 A ADDRESS 30A TEXT('Address line') 03 A COLHDG('Address' 'line') 04 A VARLEN 05 A CITY 20A TEXT('City') 06 A COLHDG('City') 07 A VARLEN 08 A STATE 2A TEXT('State code') 09 A COLHDG('State') 10 A POSTALCDE 10A TEXT('Post code') 11 A COLHDG('Postal' 'code') 12 A VARLEN 13 A ALIAS(POSTAL_CODE) 14 A CUSTNAME 30A TEXT('Customer name') 15 A COLHDG('Customer' 'name') 16 A VARLEN 17 A ALIAS(CUSTOMER_NAME) 18 A CUSTNBR 10A TEXT('Customer number') 19 A COLHDG('Customer' 'number') 20 A VARLEN 21 A ALIAS(CUSTOMER_NUMBER)

The example above shows how I would define fields within a reference file. All would be defined with their length and type, of course, and the field text, column heading, and an alias. Originally I defined fields with aliases as when I would download the file using Client Access the alias would be the column heading in the downloaded file. More recently the alias can be used in your RPG code.

When I define a file, in this example a Customer file, I can just reference the field in the reference file, rather than have to remember the size of each field.

01 A REF(REFFILE) 02 A R CUSTOMERR 03 A CUSTNBR R 04 A CUSTNAME R 05 A ADDRESS1 R REFFLD(ADDRESS) 06 A TEXT('Address line 1') 07 A COLHDG('Address' 'line 1') 08 A ALIAS(ADDRESS_1) 09 A ADDRESS2 R REFFLD(ADDRESS) 10 A TEXT('Address line 2') 11 A COLHDG('Address' 'line 2') 12 A ALIAS(ADDRESS_2) 13 A ADDRESS3 R REFFLD(ADDRESS) 14 A TEXT('Address line 3') 15 A COLHDG('Address' 'line 3') 16 A ALIAS(ADDRESS_3) 17 A CITY R 18 A STATE R 19 A POSTALCDE R

Line 1: The REF file level keyword gives the name of the reference file.

Lines 3 and 4: The R reference column when defining a field tells the compiler to use the same named fields in the reference to define this field.

Lines 5 – 16: I want to have more than one line of address in my file. I need to give each address field an unique name, ADDRESS1, ADDRESS2, ADDRESS3, and refer to the ADDRESS field in the reference file using the REFFLD keyword. I also want each of the address fields to have its own unique text, column heading, and alias, therefore, I define them for each address field.

Lines 17 – 19: These three fields can be defined as they are in the reference file.

When this file is compiled the compiler will "look" at the reference file and take the definition from it.

A display file is just another file, therefore I can use referencing in it to define the fields within.

01 A REF(CUSTOMER) 10 A R SCREEN 11 A 1 31'R P G P G M . C O M' 12 A DSPATR(HI) 13 A 3 3'Customer number .' 14 A CUSTNBR R B 3 22REFFLD(CUSTOMERR/CUSTNBR - 15 A *LIBL/CUSTOMER) 16 A 4 3'Customer name . .' 17 A CUSTNAME R B 4 22REFFLD(CUSTNAME CUSTOMER) 18 A 6 3'Address . . . . .' 19 A ADDRESS1 R B 6 22 20 A ADDRESS2 R B 7 22 21 A ADDRESS3 R B 8 22 22 A 9 3'City . . . . . . .' 23 A CITY R B 9 22 24 A 10 3'State . . . . . .' 25 A STATE R B 10 22 26 A 11 3'Postal code . . .' 27 A POSTALCDE R B 11 22

Line 1: In this situation I am using the Customer file as the reference file as all of these fields are in the Customer file.

Lines 14 and 15: If you define a field with a reference in SDA it adds all this extra code of: (file_member/field library/file)

Line 17: If I have references coming from multiple files I will not give a single reference like the first line. I will define each field with the file I am taking the data from. As you see all I need to give is: (field file)
I do this so it becomes documentation within the file as to where the value for that field comes from.

Lines 19 – 27: As there is a reference file at the top of the program these fields are defined to be the same as the same named field in the Customer file.

This example of a printer file gives a little more complicated example.

01 A R CUSTHDR SPACEB(002) 02 A CUSTNBR R 1REFFLD(CUSTNBR CUSTOMER) 03 A CUSTNAME R +1REFFLD(CUSTNAME CUSTOMER) 04 A CITY R +1REFFLD(CITY CUSTOMER) 05 A STATE R +1REFFLD(STATE CUSTOMER) *------------------------------------------------------------- 06 A R CUSTDTL SPACEB(001) 07 A ORDERNBR R 10REFFLD(ORDERNBR ORDHDR) 08 A ORDERSEQ R +1REFFLD(ORDERSEQ ORDDTL) 09 A PARTNBR R +3REFFLD(PARTNBR ORDDTL) 10 A ORDERQTY R +1REFFLD(ORDERQTY ORDDTL) 11 A ORDERAMT R +1REFFLD(ORDERAMT ORDDTL) *------------------------------------------------------------- 12 A R CUSTTOT SPACEB(001) 13 A 5'Count :' 14 A TOTALCOUNT 6 0 5EDTCDE(J) 15 A +3'Amount :' 16 A TOTALAMT R +3 +1REFFLD(ORDERAMT ORDDTL)

Here I have three record formats:

1. CUSTHDR lines 1 – 5
2. CUSTDTL lines 6 – 11
3. CUSTTOT lines 12 – 16

Here I have fields coming from multiple files:

1. CUSTOMER
2. ORDHDR
3. ORDDTL

I have defined the fields with the reference field and file they are found in. As I said before I think this makes it easier for someone else to understand where the data for that file is coming from.

Line 16: The +3 in the size column tells the compiler that this fields will be three bigger than reference field. If ORDERAMT in ORDDTL is defined as 7,2 then TOTAMT in the printer file will be 10,2.

At the start of this post I mentioned that I can do the same for variables in RPG programs. I can either use a field from a file or another variable when defining a field.

Below is a snippet of RPG3 code.

01 C MOVE 0 FROM 72 02 C *LIKE DEFN FROM TOFLD1 03 C *LIKE DEFN FROM TOFLD2+ 2

Line 1: The variable FROM is defined as 7,2.

Line 2: The variable TOFLD1 is defined to be the same as the field FROM.

Line 3: The variable TOFLD2 is defined as two bigger than FROM, in other words it will be 9,2.

I have given this example in response to the person who assured me that you could not do this in RPG3.

I am so glad I don't have to use the RPG3 method of creating one field like another. In RPG fixed format definitions they could look like:

01 D FromField S 7 2 02 D ToField1 S like(FromField) 03 D ToField2 S +2 like(FromField)

Using free format definitions the equivalent code would look like:

01 dcl-s FromField packed(7:2) ; 02 dcl-s ToField1 like(FromField) ; 03 dcl-s ToField2 like(FromField:+2) ;

I guess I can include externally described data structures that I use a lot as data structure arrays especially when performing Fetches in SQL RPG programs. As I have written about them in other posts I will not give details here.

One of the questions was could I define a DDL (SQL) table using a field reference file. Yes, it possible here is the Customer file as a DDL table.

01 CREATE OR REPLACE TABLE MYLIB.CUSTOMER AS 02 (SELECT CUSTNBR, 03 CUSTNAME, 04 ADDRESS AS ADDRESS1, 05 ADDRESS AS ADDRESS2, 06 ADDRESS AS ADDRESS3, 07 CITY, 08 STATE, 09 POSTALCDE 10 FROM REFFILE) 11 DEFINITION ONLY 12 INCLUDING COLUMN DEFAULTS 13 RCDFMT CUSTOMERR ; -- Table description 14 LABEL ON TABLE CUSTOMER IS 'Customer master' ; -- Column headings 15 LABEL ON COLUMN CUSTOMER ( 16 ADDRESS1 IS 'Address 1', 17 ADDRESS2 IS 'Address 2', 18 ADDRESS3 IS 'Address 3' 19 ) ; -- Text 20 LABEL ON COLUMN CUSTOMER ( 21 ADDRESS1 TEXT IS 'Address line 1', 22 ADDRESS2 TEXT IS 'Address line 2', 23 ADDRESS3 TEXT IS 'Address line 3' 24 ) ;

As I had to do in the DDS example as I want three address fields I need to define ADDRESS1, ADDRESS2, and ADDRESS3 I need to define them based on the reference field ADDRESS, lines 4 – 6. Then I need to give the new fields unique column headings and text, lines 15 – 24.

If you are wanting to create reference file like functionality in DDL (SQL) you ought to consider using User Defined Types to do the same type of thing.

In this example these would be the User Defined Types I would create.

CREATE TYPE MYLIB.ADDRESS AS VARCHAR(30) ; CREATE TYPE MYLIB.CITY AS VARCHAR(20) ; CREATE TYPE MYLIB.STATE AS CHAR(2) ; CREATE TYPE MYLIB.POSTAL_CODE AS VARCHAR(10) ; CREATE TYPE MYLIB.CUSTOMER_NUMBER AS VARCHAR(10) ; CREATE TYPE MYLIB.CUSTOMER_NAME AS VARCHAR(30) ;

When defining the DDL table where I could put the data type, for example VARCHAR(10), I use the User Defined Type name instead, CUSTOMER_NUMBER.

CREATE OR REPLACE TABLE MYLIB.CUSTOMER ( CUSTOMER_NUMBER FOR COLUMN "CUSTNBR" CUSTOMER_NUMBER NOT NULL, CUSTOMER_NAME FOR COLUMN "CUSTNAME" CUSTOMER_NAME NOT NULL, ADDRESS_1 FOR COLUMN "ADDRESS1" ADDRESS NOT NULL, ADDRESS_2 FOR COLUMN "ADDRESS2" ADDRESS, ADDRESS_3 FOR COLUMN "ADDRESS3" ADDRESS, CITY CITY NOT NULL, STATE STATE NOT NULL, POSTAL_CODE FOR COLUMN "POSTALCDE" POSTAL_CODE NOT NULL ) RCDFMT CUSTOMERR ;

In the above example I have omitted the code for the table description, column headings I would normally have included.

 

This article was written for IBM i 7.3, and should work for earlier releases too.