Wednesday, November 26, 2014

Using Alias for longer field names


 

Update – December 17, 2014

Thanks to a PTF there is now a much easier way to use the ALIAS names in your RPG programs than the way described below.

Read Using aliases can be simple.

 


using alias in rpg

The ALIAS keyword in DDS has been around for many, many years. I first used it in a programming environment where most of other the programmers used Cobol. They explained that if I added a ALIAS keyword to any files I created then they could use the longer alias name rather than the field name. I have to admit that the ability to use a field or variable name that was longer than six characters greatly appealed to me. This was the days of RPGIII and I had to deal with field names like J4UEEC for "End user currency", rather than the alias END-USER-CURRENCY that the Cobol programmers could use (you have to use the underscore in the alias name and the Cobol compiler translate it to a dash). The other thing I was jealous of the Cobol programmers was the Data division being the only place to define program variables.

I also found that if you used the Client Access "Data Transfer from System i" function to save an AS400/IBM i file to a Microsoft Excel file on your PC the alias names would be used as the worksheet's column headings. Which became another reason to define aliases in my files' DDS, even though I could not use them in my RPG programs.

RPG did get its equivalent of the Data division, as the Definition specification or D-spec, when RPGLE was launched. But the ability to use the alias in an RPG program came much later, in IBM i 7.1.

When coding DDS the ALIAS is just another keyword you can use, I have used included it in the source code for physical files:

A          R TESTFILER
A            FLD001        10          ALIAS(FIRST_FIELD)
A            FLD002         3P 0       ALIAS(SECOND_FIELD)
A            FLD003        10          ALIAS(THIRD_FIELD)
A            FLD004         5P 0       ALIAS(FOURTH_FIELD)
A          K FLD001

And you can do the same in the source code for logical files too:

A          R TESTFILELR                PFILE(TESTFILE)
A            FLD001                    ALIAS(FIELD_1)
A            FLD002                    ALIAS(FIELD_2)
A            FLD003                    ALIAS(FIELD_3)
A            FLD004                    ALIAS(FIELD_4)
A          K FLD002
A          K FLD001

Below is a small program I created to show how to use the alias names rather than the files' field names. It also demonstrates what I think is one of the cool new features of the all free RPG, that I can mingle definition specifications.

01  dcl-f TESTFILE usage(*input) alias qualified ;
02  dcl-ds PhyFile likerec(TESTFILE.TESTFILER) ;

03  dcl-f TESTFILEL usage(*input) alias qualified ;
04  dcl-ds LglFile likerec(TESTFILEL.TESTFILELR:*input) ;

05  dcl-ds Another
      extname('TESTFILE') alias qualified
    end-ds ;

06  read TESTFILE PhyFile ;
07  read TESTFILEL LglFile ;

08  Another = PhyFile ;

09  *inlr = *on ;

When I define the file TESTFILE with the DCL-F, line 1, I have to use the ALIAS and QUALIFIED keywords. Then on the line below I define a data structure, called PhyFile, with DCL-DS with the LIKEREC keyword. The LIKEREC defines the sub-fields in the data structure to be the same as the fields in the record format given, in this case the record format TESTFILER in the file TESTFILE. As I am only using this file for input I do not have to give the types of fields to be included as the default is *INPUT. As the LIKREC is not the subject for this post I will direct you to the appropriate part of IBM's documentation, which you will find near the bottom of this post.

I do the same on lines 3 and 4 for the logical file TESTFILEL and a data structure called LglFile. But this time I am using the *INPUT value in the LIKEREC, even though it is not necessary.

On line 5 I am showing how you can define a data structure, Another, which will allow me to use alias names rather field names for the fields define in TESTFILE.

Lines 6 and 7 show that when I read the two files, TESTFILE and TESTFILEL, I need to read them into the two data structures I defined on lines 2 and 4.

On line 8 I move the contents of the data structure PhyFile into the data structure Another. I put a debug break on line 9 so I could display the sub-field names of the data structures and what values were held in them:

> EVAL phyfile
  PHYFILE.FIRST_FIELD = 'first---->'
  PHYFILE.SECOND_FIELD = 222.
  PHYFILE.THIRD_FIELD = 'third---->'
  PHYFILE.FOURTH_FIELD = 44444.
> EVAL lglfile
  LGLFILE.FIELD_1 = 'first---->'
  LGLFILE.FIELD_2 = 222.
  LGLFILE.FIELD_3 = 'third---->'
  LGLFILE.FIELD_4 = 44444.
> EVAL another
  ANOTHER.FIRST_FIELD = 'first---->'
  ANOTHER.SECOND_FIELD = 222.
  ANOTHER.THIRD_FIELD = 'third---->'
  ANOTHER.FOURTH_FIELD = 44444.

Below is the pre-all free RPG equivalent of the program above:

01  FTESTFILE  IF   E           K DISK    alias qualified
02  FTESTFILEL IF   E           K DISK    alias qualified

03  D PhyFile         DS                  likerec(TESTFILE.TESTFILER)
04  D LglFile         DS                  likerec(TESTFILEL.TESTFILE1R:*input)
05  D Another       E DS                  extname('TESTFILE') alias qualified
     /free
06     read TESTFILE PhyFile ;
07     read TESTFILEL LglFile ;

08     Another = PhyFile ;

09     *inlr = *on ;

There is not an alias keyword in SQL DDL tables, but you can still use longer names. For example:

CREATE TABLE MYLIB/TESTTABLE
  (FIRST_COLUMN CHAR(10) NOT NULL,
   SECOND_COLUMN DECIMAL(3,0),
   THIRD_COLUMN CHAR(10),
   FOURTH_COLUMN DECIMAL(5,2))
   RCDFMT TESTTABLER ;

When I use the 'Display File Field Description', DSPFFD, you can see that the longer names are the Alternative names, and SQL has chopped them to create the field names:

           Data        Field
Field      Type       Length
FIRST00001 CHAR           10
  Alternative name  . . . .  FIRST_COLUMN
SECON00001 PACKED       3  0
  Alternative name  . . . .  SECOND_COLUMN
THIRD00001 CHAR           10
  Alternative name  . . . .  THIRD_COLUMN
FOURT00001 PACKED       5  0
  Alternative name  . . . .  FOURTH_COLUMN

When you create a SQL view you can set the name of the field and the alternate name:

CREATE VIEW MYLIB/TESTVIEW
  (FIELD_NUMBER_ONE FOR "FLD001",
   FIELD_NUMBER_TWO FOR "FLD002",
   FIELD_NUMBER_THREE FOR "FLD003",
   FIELD_NUMBER_FOUR FOR "FLD004",
   FIELD_NUMBER_FIVE FOR "FLD012",
   FIELD_NUMBER_SIX FOR "FLD013")
   AS SELECT A.FLD001,A.FLD002,A.FLD003,A.FLD004,
             B.FLD012,B.FLD013
        FROM TESTFILE A,TESTFILE2 B
       WHERE A.FLD001 = B.FLD010


           Data        Field
Field      Type       Length
FLD001     CHAR           10
  Alternative name  . . . .  FIELD_NUMBER_ONE
FLD002     PACKED       3  0
   Alternative name  . . . . FIELD_NUMBER_TWO
FLD003     CHAR           10
   Alternative name  . . . . FIELD_NUMBER_THREE
FLD004     PACKED       5  0
   Alternative name  . . . . FIELD_NUMBER_FOUR
FLD012     CHAR           10
   Alternative name  . . . . FIELD_NUMBER_FIVE
FLD013     PACKED       5  0
  Alternative name  . . . .  FIELD_NUMBER_SIX

The example below is a SQLRPGLE program I wrote:

01  dcl-ds ViewData
      extname('TESTVIEW') alias qualified
    end-ds ;

02  dcl-s Count int(3) ;

03  exec sql SELECT *
               INTO :ViewData FROM TESTVIEW ;

04  exec sql SELECT COUNT(SECOND_COLUMN)
               INTO :Count FROM TESTTABLE
              WHERE FIRST_COLUMN = '1' ;

05  *inlr = *on ;

On line 1 I have created a data structure, ViewData, with the alias keyword. When the SELECT statement on line 3 is executed all of the values are moved into the data structure. Now I can use the long names for those columns/sub-fields.

Line 4 shows how I can do a count of the number of records that contain a value using the long names.

As I did in my previous program I put a debug break on line 5 so I could display the sub-field names of the data structure and what values are held:

> EVAL viewdata
  VIEWDATA.FIELD_NUMBER_ONE = 'first---->'
  VIEWDATA.FIELD_NUMBER_TWO = 222.
  VIEWDATA.FIELD_NUMBER_THREE = 'third---->
  VIEWDATA.FIELD_NUMBER_FOUR = 44444.
  VIEWDATA.FIELD_NUMBER_FIVE = '12------->'
  VIEWDATA.FIELD_NUMBER_SIX = 00013.
> EVAL count
  COUNT = 1

Using the examples I can now do what those Cobol programmer were doing all those years ago, but in RPG.

IBM i 7.1 TR9 and 7.2 TR1 offer an easier way to use the ALIAS without using data structures. I am not able to test this as the IBM i servers I use do not have these PTFs. According to the documentation you can just use the ALIAS keyword in the file declaration like this to use the alias names:

01  dcl-f TESTFILE alias ;

02  read TESTFILER ;

03  if (%eof) ;
    .
    .
04  elseif (SECOND_FIELD = '222') ;

 

You can learn more about these on the IBM website:

 

This article was written for IBM i 7.2, and it should work with 7.1 too.

4 comments:

  1. I am glad to see you show how to use both System (short) names and SQL long names in SQL Views they really help with Row Change token or Row Id fields too.

    ReplyDelete
  2. >> There is not an alias keyword in SQL DDL tables, but you can still use longer names.

    When creating a table, you can specify both the SQL column name and the system-column-name in each column-definition, using the syntax "column-name FOR system-column-name". For example:

    CREATE TABLE MYLIB/MYTABLE
    (FIRST_COLUMN FOR FRSTCOL CHAR(10) NOT NULL,
    SECOND_COLUMN FOR SCNDCOL DECIMAL(3,0),
    ...
    ) RCDFMT ... ;

    http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzhctabl.htm

    ReplyDelete
  3. Sorry to come to this blog so late but this is exactly what I was looking for. Well done.

    ReplyDelete
    Replies
    1. You are never too late to join this "party". Thank you for the compliment.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.