Using column aliases in a WHERE clause

When operating in NPS compatibility mode, you can reference an expression in the WHERE clause by its alias in the select list.

Whether NPS compatibility mode is being used depends on the setting of the SQL_COMPAT global variable.

When the SQL_COMPAT='NPS' parameter is set, an expression in the select list can be referenced by its alias in the WHERE clause.

Attention: The SQL_COMPAT='NPS' feature is only available in Db2® Version 11.5 Mod Pack 1 or later versions.

The WHERE clause can contain non-correlated aliases and correlated aliases. The expression must not include an aggregate function, an OLAP specification, or the ROWNUM pseudocolumn (SQLSTATE 42903).

Examples

The following examples illustrate the use of non-correlated column aliases in the WHERE clause:

SELECT c1 as a
FROM t1
WHERE a = 5;

SELECT t1.c1 as a, t1.c2+t2.c3 as b
FROM t1 , t2
WHERE a = t2.c2;

SELECT abs(c1) as a
FROM t1
WHERE a = 4;

SELECT length(c1) as a
FROM t1
WHERE a = 5
GROUP BY c1;

The following examples illustrate the use of correlated column aliases in the WHERE clause:

select c1 as a1
FROM t1
WHERE c2 in (select c3 from t2 where c3 = a1);

select abs(c1) as a1
FROM t1
WHERE c2 in (select c3 as a3 from t2 where a3 = a1);