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