Naming result columns

You can provide your own names for the result table columns for a SELECT statement. This capability is particularly useful for a column that is derived from an expression or a function.

Procedure

Use the AS clause to name result columns in a SELECT statement.

Examples

The following examples show different ways to use the AS clause.

Example: SELECT with AS CLAUSE

The following example of the SELECT statement gives the expression SALARY+BONUS+COMM the name TOTAL_SAL.

SELECT SALARY+BONUS+COMM AS TOTAL_SAL
   FROM DSN8D10.EMP
   ORDER BY TOTAL_SAL;

Example: CREATE VIEW with AS clause

You can specify result column names in the select-clause of a CREATE VIEW statement. You do not need to supply the column list of CREATE VIEW, because the AS keyword names the derived column. The columns in the view EMP_SAL are EMPNO and TOTAL_SAL.

CREATE VIEW EMP_SAL AS
  SELECT EMPNO,SALARY+BONUS+COMM AS TOTAL_SAL
     FROM DSN8D10.EMP;

Example: set operator with AS clause

You can use the AS clause with set operators, such as UNION. In this example, the AS clause is used to give the same name to corresponding columns of tables in a UNION. The third result column from the union of the two tables has the name TOTAL_VALUE, even though it contains data that is derived from columns with different names:

SELECT 'On hand' AS STATUS, PARTNO, QOH * COST AS TOTAL_VALUE
   FROM PART_ON_HAND
UNION ALL
SELECT 'Ordered' AS STATUS, PARTNO, QORDER * COST AS TOTAL_VALUE
   FROM ORDER_PART
ORDER BY PARTNO, TOTAL_VALUE;

The column STATUS and the derived column TOTAL_VALUE have the same name in the first and second result tables. They are combined in the union of the two result tables, which is similar to the following partial output:

STATUS    PARTNO   TOTAL_VALUE
=======   ======   ===========
On hand   00557    345.60
Ordered   00557    150.50
.
.
.

Example: GROUP BY derived column

You can use the AS clause in a FROM clause to assign a name to a derived column that you want to refer to in a GROUP BY clause. This SQL statement names HIREYEAR in the nested table expression, which lets you use the name of that result column in the GROUP BY clause:

SELECT HIREYEAR, AVG(SALARY)
   FROM (SELECT YEAR(HIREDATE) AS HIREYEAR, SALARY
            FROM DSN8D10.EMP) AS NEWEMP
   GROUP BY HIREYEAR;

You cannot use GROUP BY with a name that is defined with an AS clause for the derived column YEAR(HIREDATE) in the outer SELECT, because that name does not exist when the GROUP BY runs. However, you can use GROUP BY with a name that is defined with an AS clause in the nested table expression, because the nested table expression runs before the GROUP BY that references the name.