Kim Moore <[email protected]> writes: > I am working on a query that uses a user defined function in a group > by clause. > > select myfunction (datecolumn) > ,count(*) > from table > group by myfunction (datecolumn) > > Executing the query gives the error "The SELECT list of a group query > contains at least one invalid expression." > > When I replace myfunction (user defined function) with a DATE function > which comes standard with Derby, the query works. > > All help is greatly appreciated.
I think this is because user-defined functions are not known to be deterministic. See this discussion for more details: http://www.nabble.com/Functions-in-GROUP-BY-expressions--%28related-to-DERBY-883%29-tf2517296.html Derby 10.5 will support the DETERMINISTIC keyword in CREATE FUNCTION statements (https://issues.apache.org/jira/browse/DERBY-3570), but I haven't tested if that will actually allow you to use user-defined functions in GROUP BY. Putting the function call in a subquery and renaming the column holding the value returned by the function should work even without the DETERMINISTIC keyword, though: select x, count(*) from (select myfunction(datecolumn) from mytable) t(x) group by x -- Knut Anders
