Example: Ordering By a Column Alias Name - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

You cannot explicitly order by a column alias name. You must specify the column position of the column aliased by the name in the select list.

Suppose you have the following base table definition:

   CREATE TABLE t1
    (i INTEGER,
     j INTEGER,
     k INTEGER);

The following query against this table fails because it specifies ordering on the column alias name j, which the system interprets as column j in table t1 rather than as the expression SUM(t1.j):

   SELECT t1.i, SUM(t1.j) AS j, t1.k
   FROM t1
   GROUP BY 1,3
   ORDER BY j;
   *** Failure 3504 Selected non-aggregate values must be part of the associated group.

The following rewrite of the query successfully performs the desired ordering:

   SELECT t1.i, SUM(t1.j) AS j, t1.k
   FROM t1
   GROUP BY 1,3
   ORDER BY 2;