Using alias for data structure subfields

Have you ever used a file or table to externally define a data structure, and then wondered how to use the alias field/column names rather than the short ones?

Fortunately it is simple to do. But, before I start with showing the file and table I will be using in these examples. Let me start with the file, TESTFILE:

A R TESTFILER A F001 7A ALIAS(FIRST_FIELD) A F002 7P 2 ALIAS(SECOND_FIELD) A F003 L ALIAS(DATE_FIELD) A F004 T ALIAS(TIME_FIELD)

Defining an alias in a DDS file is straightforward, I just use the ALIAS keyword.

My example DDL table I have deliberately made very simple:

CREATE OR REPLACE TABLE MYLIB.TESTTABLE ( COLUMN_1 FOR "C001" CHAR(7), COLUMN_2 FOR "C002" DECIMAL(7,2), DATE_COLUMN FOR "C003" DATE, TIME_COLUMN FOR "C004" TIME ) ;

The short names will be used by RPG programs, and the (long) column names become the aliases.

Now I have shown the file and table this is a typical RPG showing how I use them both to define data structures.

01 **free 02 ctl-opt option(*srcstmt) alwnull(*usrctl) ; 03 dcl-ds Data1 extname('TESTFILE') end-ds ; 04 dcl-ds Data2 extname('TESTTABLE') qualified dim(99) ; 05 end-ds ; 06 exec sql SET OPTION DATFMT = *ISO, TIMFMT = *ISO ; 07 *inlr = *on ;

Line 1: I now only program in totally free RPG.

Line 2: I have used two control options. The first ensures that the program internal lines numbers will match the source sequence numbers. The second allows for me to control the handling of null within my program. I have to able to handle null as my table could return null values.

Line 3: The first data structure, Data1, is defined using the file, TESTFILE. Notice that the END-DS is on the same line as the rest of the data structure definition. I just wanted to show that this was possible, even though I prefer to have the END-DS on its own line.

Line 4 and 5 : The data structure Data2 is defined using the table, TESTTABLE. As line 4 ends with a semicolon the END-DS must be on its own line.

Line 6: As the TESTTABLE contains a date column and a time column I need to define what format the date and time are in. I use the SET OPTION statement to do this. Failure to do this can make Data2 unusable to receive results from a SQL Fetch statement.

When I compile the program and I look in the source listing I can see that the fields/columns from the file and table have been included as subfield in the data structures, but the short names have been used.

dcl-ds Data1 extname('TESTFILE') end-ds ; *------------------------------------------------------ * Data structure . . . . . . : DATA1 * External format . . . . . : EG007R : MYLIB/TESTFILE *------------------------------------------------------ <---------------------- Source Specifications ---------- ....1....+....2....+....3....+....4....+....5....+....6. D F001 7A D F002 7P 2 D F003 10D DATFMT (*ISO-) D F004 8T TIMFMT (*ISO.) dcl-ds Data2 extname('TESTTABLE') qualified dim(99) ; end-ds ; *---------------------------------------------------------- * Data structure . . . . . . : DATA2 * External format . . . . . : TESTTABLE : MYLIB/TESTTABLE *---------------------------------------------------------- <---------------------- Source Specifications -------------- ....1....+....2....+....3....+....4....+....5....+....6....+ D C001 7A D C002 7P 2 D C003 10D DATFMT (*ISO-) D C004 8T TIMFMT (*ISO.)

To use the long names all I have to do is to use the ALIAS in the data structure definitions, see lines 3 and 4, below.

01 **free 02 ctl-opt option(*srcstmt) alwnull(*usrctl) ; 03 dcl-ds Data1 extname('TESTFILE') alias end-ds ; 04 dcl-ds Data2 extname('TESTTABLE') qualified alias dim(99) ; 05 end-ds ; 06 exec sql SET OPTION DATFMT = *ISO, TIMFMT = *ISO ; 07 *inlr = *on ;

Now when I compile the program and look at the compile listing I can see that the aliases have been used.

dcl-ds Data1 extname('TESTFILE') alias end-ds ; *------------------------------------------------------ * Data structure . . . . . . : DATA1 * External format . . . . . : EG007R : MYLIB/TESTFILE *------------------------------------------------------ <---------------------- Source Specifications ---------- ....1....+....2....+....3....+....4....+....5....+....6. D FIRST_FIELD 7A D SECOND_FIELD 7P 2 D DATE_FIELD 10D DATFMT (*ISO-) D TIME_FIELD 8T TIMFMT (*ISO.) dcl-ds Data2 extname('TESTTABLE') qualified alias dim(99) ; end-ds ; *---------------------------------------------------------- * Data structure . . . . . . : DATA2 * External format . . . . . : TESTTABLE : MYLIB/TESTTABLE *---------------------------------------------------------- <---------------------- Source Specifications -------------- ....1....+....2....+....3....+....4....+....5....+....6....+ D COLUMN_1 7A D COLUMN_2 7P 2 D DATE_COLUMN 10D DATFMT (*ISO-) D TIME_COLUMN 8T TIMFMT (*ISO.)

Now I must the alias names for the subfields in the program, as the short names have not been defined.

 

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