ORA-00979: not a GROUP BY expression when using LISTABC function in SQL

Issue Description

Getting "ORA-00979: not a GROUP BY expression when using LISTABC function in SQL" when using a parameter in GROUPBY.

Resolution

The main reason the query doesn't work in JR is the lack of support for named parameters/bind variables in JDBC prepared statements.

A query like "select case when orders like $P

{param} then .. group by case when orders like $P{param}

then .." is executed via JDBC as "select case when orders like ? then .. group by case when orders like ? then .."

The two prepared statement parameters would be assigned the same value, but the DB engine cannot know a priori when parsing the query, therefore it sees the the expression in the select clause as different from the expression in the group by clause. Hence the error.

One option is to use a subquery. Something like select X from (select (case when orders like $P{param} then ..) as X from ..) group by X

Another solution is to use $P!.

Ref. Case 00067527