Jump to content
Changes to the Jaspersoft community edition download ×
  • ORA-00979: not a GROUP BY expression when using LISTABC function in SQL


    Jim W

    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


    User Feedback

    Recommended Comments

    There are no comments to display.



    Guest
    This is now closed for further comments

×
×
  • Create New...