Expressions refer to column aliases

When operating in NPS compatibility mode, an expression can refer to column aliases that are set in the select list.

Whether you are operating in NPS compatibility mode depends on the setting of the SQL_COMPAT global variable:
  • When SQL_COMPAT='NPS', an expression can refer to either a column name or a column alias that is set in the select list. The column resolution order for an unqualified column reference in a select list is:
    1. Resolve as a procedure argument or variable references.
    2. Resolve in the input tables of the current operation.
    3. Resolve against column aliases that appear before this reference in the select list.
    4. Resolve as a correlated column reference.
    5. Resolve as a trigger reference.
    6. Resolve in an external reference table (used by LOAD).
    7. Resolve as a global variable reference.
  • Otherwise, a grouping expression can refer only to a column name.

Examples

The following examples illustrate the use, in an expression, of column aliases (a and b) that are set in the select list:
SELECT c1 AS a, a+3 AS b FROM t1;

SELECT c1 AS a, ABS(a) AS b FROM t1 GROUP BY a, b HAVING c1 < 0;

SELECT c1+c3 AS a, CASE WHEN a < 5 THEN a ELSE NULL END AS b FROM t1;