Create Alias

Tables or columns may use an alias

There are two types of aliases that are used most frequently in SQL commands; a column alias and a table alias. There are a variety of reasons that alias may be helpful when querying SQL commands:

  • - alias in a long query can make your query easier to read and understand
  • - alias table is use when using a same table in one query
    • - alias column is to identify the column naming when used together with aggregate functions, then the column is easily understood 


>>-CREATE------------+-ALIAS--alias name--FOR--+-Table Name-+------>
        | OR REPLACE |                         | View Name  |    
        +------------+                         +------------+      

   >------+----------------+-------------------------------------><
          | partition name |
          +----------------+
          | member name    |
          +----------------+

OR REPLACE will replace the definition for the alias if one exists on the current server. The existing definition is dropped before the new definition is create in the catalog with the exception that privileges that were granted on the alias are not affected. This option has no effect if no definition for the alias exists on the current server.

Multiple member files were quite common in IBM midrange computers from the S/36, S38, to the AS/400, and even today on the Power i. SQL was not equipped to deal with these type file objects until OS/400 R430, which added support of an SQL alias statement. DDS has little trouble defining multiple member files, and the OVRDBF command easily addresses accessing various members, or all members of a physical file. DDL defined tables do not accommodate multiple member tables--it is a foreign concept. In order to address multiple member files, SQL requires an alias for each of the members that must be accessed. Once the alias has been created, the application must reference the alias to retrieve the data. The alias is a persistent object -- it must be created only once. The member referenced in the CREATE ALIAS does not have to exist when the ALIAS is created. Any SQL tool, such as OS/400 or i5/OS interactive SQL (STRSQL) or Operations Navigator, can be used to create the alias.

.
.
.
CREATE ALIAS MYLIB.FILE1MBR1 FOR MYLIB.MYFILE(MBR1)
CREATE ALIAS MYLIB.FILE1MBR2 FOR MYLIB.MYFILE(MBR2)
.
.
.

Refrence the Alias

To access a specific member of the physical file data, the application must specifies the appropriate alias. Simplistically, for example, MYLIB.FILE1MBR1 or MYLIB.FILE1MBR2, depending on which member should be accessed. For examples of using RUNSQL click the button below.

.
.
.
runsql stm('CREATE ALIAS mblib/adaterpmbr FOR mblib/qrpglesrc(adaterp)')
.
.
.
execsql stmt('select * from mblib/adaterpmbr')
.
.
.