Using column aliases in a HAVING clause

When operating in NPS compatibility mode, you can specify the exposed name of a SELECT clause column in the HAVING clause of a query.

Whether NPS compatibility mode is being used depends on the setting of the SQL_COMPAT global variable:
  • When SQL_COMPAT='NPS', a HAVING clause can refer to a column of a SELECT clause by either its name or its exposed name.
  • Otherwise, a HAVING clause can refer to a column of a SELECT clause only by its name, not by its exposed name.

Examples

The following examples illustrate the use of exposed names of SELECT clause columns in having clauses when SQL_COMPAT='NPS':
SELECT c1 as a, COUNT(*) as c
FROM t1
GROUP BY c1 having a > 20 and c > 10;

SELECT t1.c1 as a, t1.c2+t2.c3 as b, COUNT(*) as c
FROM t1 JOIN t2 ON t1.c1 = t2.c1
GROUP BY t1.c1, b having a+5 = 10 ;

SELECT var(c1) as a
FROM t1
GROUP BY c1 having a > 200