HPlogo Communicator e3000 MPE/iX Express 1 Based on Release 7.0 > Chapter 6 Technical Articles

AS Clause Enhancement in Allbase/SQL.

MPE documents

Complete PDF
Table of Contents
Index

By Sekhar N.D
CSY - Databases

Objectives of the Enhancement

The objective of the enhancement is to allow 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.

Usage of Column Alias Name

The syntax of the Select Statement with Alias Name: 

 ==========     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 "".

NOTE: Key word FROM can be used as Column alias names, only when it is enclosed within Quotes ("FROM")

  • SELECT COL1 FROM FROM TABLE; (Not Allowed)

  • SELECT COL1 "FROM" FROM TABLE; (Allowed)

Constraints

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

  2. Column Alias Name cannot be used in subqueries because the subquery result is not returned to the user and hence the alias name doesn't have any significance here.

  3. Column Alias Name 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

Column Alias cannot be used in sub-queries. (DBERR 1172)
Level: 1172

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

AS Clause cannot be used in the Select Statement of Create View,

Type 2 Insert or Genplan. (DBERR 1173)
Level: 1173

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

Expected something between the single quotes. (DBERR 1174)
Level: 1174

This error occurs when the alias name is specified as a single-quoted identifier and the identifier doesn't contain any characters, i.e., the alias name is specified as ''"

Column Alias name cannot be only spaces. (DBERR 1175)
Level: 1175

This error occurs when the alias name is specified as a single-quoted identifier or as a double-quoted identifier and the identifier is made of only spaces.

Column Alias Name cannot be used in the Select Statement of Create View, Type 2 Insert or Genplan. (DBERR 1176)
Level: 1176

This error occurs when Alias Name is specified in the Select Statement of Create View, Type 2 Insert or Genplan.



Chapter 6 Technical Articles

MPE/iX Release 6.5 Supports >3.75 GBs of Memory