Hi all,
I'd like to be able to use a column alias (from the column list of the
SELECT statement) in the HAVING clause. When I try, I get the following
error:
ij> select 0 as foo from rita.request having foo;
ERROR 42X04: Column 'FOO' is either not in any table in the FROM list or
appears within a join specification and is outside the scope of the join
specification or appears in a HAVING clause and is not in the GROUP BY
list. If this is a CREATE or ALTER TABLE statement then 'FOO' is not a
column in the target table.
Of course, this is a trivial example, but in my real-life case I have an
aggregate function being given an alias:
SELECT ... SUM(mo2_.ltu_qty) as req_line_ledger_balance ... GROUP BY
... HAVING (req_line_ledger_balance <> 0)
If I include the SUM(...) expression again in the HAVING clause,
presumably Derby will evaluate it a second time, not knowing that it's
already evaluated it and the result is in the selected columns?
I understand the purpose of the restriction and the error message,
"appears in a HAVING clause and is not in the GROUP BY list," but
aggregate functions do not have to be in the GROUP BY list in order to be
available in time for HAVING to operate on them, as the values are in the
output set.
Derby clearly knows this, as using the expression instead of the alias
works:
ij> select sum(ltu_qty) AS sum_ltu_qty from rita.movement group by
move_site_id having sum(ltu_qty) > 0;
SUM_LTU_QTY
-----------
0 rows selected
ij> select sum(ltu_qty) AS sum_ltu_qty from rita.movement group by
move_site_id having sum_ltu_qty > 0;
ERROR 42X04: Column 'SUM_LTU_QTY' ... appears in a HAVING clause and is
not in the GROUP BY list...
I would try to write a patch myself, but I'd need some help to get Derby
to compile in Eclipse so that I can debug it.
Cheers, Chris.
--
Aptivate | http://www.aptivate.org | Phone: +44 1223 760887
The Humanitarian Centre, Fenner's, Gresham Road, Cambridge CB1 2ES
Aptivate is a not-for-profit company registered in England and Wales
with company number 04980791.
