I am using NamedParameterJdbcTemplate to run a SQL in Oracle. It runs absolutely fine when run in the Oracle database, while passing the params in SQL developer, but it gives a nested exception while using the JdbcTemplate

java.sql.SQLSyntaxErrorException: ORA-00979: not a GROUP BY expression

I am passing the params through a map as given below.

select to_char(c.created_dt, :GROUP_FORMAT) as point
      ,count(*) as CNT
  from tableA c
 where trunc(c.created_dt) >= to_date(:START_DATE,'YYYY-MM-DD')
   and trunc(c.created_dt) <= to_date(:END_DATE,'YYYY-MM-DD')
 group by to_char(c.created_dt, :GROUP_FORMAT)

return jdbcTemplate.query(sql,
         ImmutableMap.<String, Object>builder()
           .put("GROUP_FORMAT", groupFormat)
           .put("END_DATE", Date.valueOf(endDate))                                                                  
           .put("START_DATE", Date.valueOf(startDate))
           .build(), (rs, rowNum) -> {
             ClassToMap data = ClassToMap.builder().point(rs.getString(1)).count(rs.getInt(2)).build();
             return data;});

The issue is mainly with the GROUP_FORMAT parameter, have verified hard-coding the GROUP_FORMAT and it works fine. The values of GROUP_FORMAT passed will be 'YYYYMMDD' or 'YYYYMM'

Expect the output to be an array of ClassToMap objects.

2 Answers

AlexGera On Best Solutions

JdbcTemplate does not make just string concatenation but a piece of wrapping for parameters. So, you won't be able to pass them to 'synthetic' expressions like 'to_char'. Alternatively you can group by the same not formatted column which should give you the same result hopefully.

I've tested it working on the following code:

String groupFormat = "YYYYMMDD";

String sql = "select c.created_dt, to_char(c.created_dt, :groupFormat) as point" + 
        "      ,count(*) as CNT" + 
        "  from your_table c" + 
        " group by c.created_dt";

MapSqlParameterSource namedParameters = new MapSqlParameterSource();
namedParameters.addValue("groupFormat", groupFormat);

dbSelector.gssNamedJdbcTemplate().query(sql, namedParameters, rs -> {

Anoop Js On

The issue was same as shared by racraman in the comments. Changing the SQL to accept the param in only one place resolved the issue