HPlogo Communicator HP e3000 MPE/iX Express 2 Based on Release 6.5 > Chapter 3 Technical Articles

Using the AS Clause in ALLBASE/SQL

MPE documents

Complete PDF
Table of Contents
Index

By Kumaran N.S.
Commercial Systems Division - Database

The AS Clause allows users to specify an alias name for the items in the Select Statement. The specified alias name will be returned as the column heading in the query result.

Syntax

  -- Select Statement Level --
  [BULK] QueryExpression [ORDER BY {ColumnID [ASC ]} [,...]]
                         [         {         [DESC]}       ]
  -- Subquery Level --
  (QueryExpression)
  -- Query Expression Level --
  {QueryBlock          }  [UNION [ALL] {QueryBlock       }] [...]
  {(QueryExpression)} [                {(QueryExpression)}]
  -- Query Block Level --
  SELECT [ALL     ] SelectList [INTO HostVariableSpecification]
         [DISTINCT]
  FROM FromSpec [,...] [WHERE SearchCondition1]
    [GROUP BY GroupColumnList]
    [HAVING SearchCondition2]
  where SelectList =
    {*                                       }
    {[Owner.]Table.*                         }
    {CorrelationName.*                       } [,...]
    {Expression AS Alias_name                }
    {[[Owner.]Table.]ColumnName AS Alias_name}
    {CorrelationName.ColumnName AS Alias_name}
  where Alias_name can be an identifier,
  a single-quoted string or a double-quoted string

Example

SELECT PartNumber AS "Part Number" , AVG (UnitPrice) AS avg_price, AVG (deliverydays) AS 'avg days' FROM PurchDB.SupplyPrice GROUP BY partnumber;

If the alias name is specified as an identifier it should conform to the following rules which are the rules defined for "Basic Names" in ALLBASE/SQL.

  • The name can be up to 20 characters in length.

  • The name can be made of any combination of letters (a to z, A to Z), decimal digits (0 to 9), $, #, @, or underscore (_). The first character cannot be an underscore or a decimal digit.

When the alias name is specified as a single-quoted identifier or as a double-quoted identifier, it can contain spaces and special characters in addition to the characters allowed in the "Basic Names," as defined above, and the name can be up to 20 characters in length. The alias name cannot be only spaces and it should not be of zero length, i.e., it cannot be specified as '' or as "".

Constraints

  • The alias name cannot be used in the other clauses of the query.

  • AS Clause cannot be used in sub-queries because the sub-query result is not returned to the user and hence the alias name doesn't have any significance here.

  • AS Clause cannot be used in the Select Statement of Create View, Type 2 Insert or Genplan because it doesn't have any significance here.

New Error Messages

1172
Message

AS Clause cannot be used in sub-queries. (DBERR 1172)

Cause

This error occurs when AS Clause is specified in sub-queries.

Action

Remove the AS Clause from the sub-query.

1173
Message

AS Clause cannot be used in the Select Statement of Create View. (DBERR 1173)

Cause

AS Clause is specified in the Select Statement of Create View, Type 2 Insert or Genplan.

Action

Remover AS Clause from Select Statement of Create View, etc.

1174
Message

Expected something between the single quotes. (DBERR 1174)

Cause

The alias name is specified as a single-quoted identifier that doesn't contain any characters, i.e., the alias name is specified as ' '

Action

Insert at least one legal character between the qoutes.

1175
Message

The alias name cannot be only spaces. (DBERR 1175)

Cause

The alias name specified is either a single-quoted identifier or as a double-quoted identifier made of only spaces.

Action

Insert at least one legal character between the qoutes that is not a space.



QUERY/iX Enhancements Respond to User Requests

Apache 1.3.9 for MPE/iX