0

Table structure -

create table test
(month integer,
year integer,
thresholds decimal(18,2)
);

Static insert for simulation -

insert into test(month,year,threshold) values(4,2021,100),(5,2021,98),(6,2021,99);

If I query postgres database using anorm, it works for regular queries. However on adding aggregate functions like max, the RowParser is not able to find the alias column.

val queryString =
  """select max(month) as monthyear from test
     | where (month || '-' || year)
     | = {inQuery}""".stripMargin

    val inQuery1 = "'5-2021'"

The below on method causes the issue -

val latestInBenchmark = SQL(queryString).on("inQuery" -> inQuery1) // removing the on resolves the problem

logger.info("query for latest period ---> " + latestInBenchmark)

val latestYearMonthInInterval = database.withConnection(implicit conn => {
          latestInBenchmark.as(SqlParser.int("monthyear").*)
        })

Removing the on rectifies the problem and SqlParser.int(column-name) works as expected. This also does not affect queries that use count aggregate function.

Error encountered :

(Validated intervals with sorting -> ,Failure(anorm.AnormException: 'monthyear' not found, available columns: monthyear, monthyear))
[error] c.b.ThresholdController - 'monthyear' not found, available columns: monthyear, monthyear
ForeverLearner
  • 1,901
  • 2
  • 28
  • 51
  • 1
    You should never ever use string interpolation with parameter, please first read the documentation – cchantep Jul 20 '21 at 16:20
  • Maybe OP changed the post in the meantime but there's no string interpolation in here. – Gaël J Jul 21 '21 at 06:03
  • It happens without the string interpolation. It was a copy-paste error, since I used string interpolation to workaround the Anorm parameter error – ForeverLearner Jul 21 '21 at 06:22

1 Answers1

-1

The error you have is a bit misleading but it means the query either returns a row with a null value or no row.

In your case I think the issue is the WHERE clause: you have put single quotes around the value but Anorm will do it by itself when using .on(...) or Anorm interpolation.

Thus, replace:

val inQuery1 = "'5-2021'"

By:

val inQuery1 = "5-2021"
Gaël J
  • 11,274
  • 4
  • 17
  • 32
  • The sql works and returns a row. Tried removing the single-quotes, but gives me the same error. – ForeverLearner Jul 21 '21 at 05:30
  • @ForeverLearner, then I'd try to debug what's going on in the response parsing. You should be able to have access to the raw data returned by the query and maybe understand the error. – Gaël J Jul 21 '21 at 06:02
  • If you read the [documentation](http://playframework.github.io/anorm/#passing-parameters), there are many examples how to pass parameters, clearly denoting you must not quote by yourself. As any decently typed lib, Anorm expect an `Option[T]` if you want to pass a nullable `T`. – cchantep Jul 23 '21 at 09:44