0

I have a batch job that I am testing with in-memory H2 database. I have a reader that works perfectly fine with a database installed on my computer. But when I test it using H2, I get this error:

org.springframework.jdbc.BadSqlGrammarException: Attempt to process next row failed; bad SQL grammar [select name, age, status, COUNT(STATUS) from myTable]; nested exception is org.h2.jdbc.JdbcSQLException: Column "COUNT(status)" not found [42122-197]

This is my reader. I don't know why I am getting above error. 

JdbcCursorItemReader<myObject> reader = new JdbcCursorItemReader<>();
reader.setSql("select name, age, status, COUNT(STATUS) from myTable
where name="something",
group by name, age, status
order by name, age, status);
Faraz
  • 6,025
  • 5
  • 31
  • 88
  • Your group by is not valid SQL. Read: https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc – Madhur Bhaiya Nov 08 '18 at 20:04
  • @MadhurBhaiya hi, thanks for the response. Actual query is long and correct. I posted this query just as an example. – Faraz Nov 08 '18 at 20:28

1 Answers1

1

It sounds like some processor added backtics around COUNT(STATUS).

COUNT(STATUS) may not be what you wanted. For each row (with name="something" for each age), check STATUS for being NOT NULL, then deliver the count of how many are not null.

COUNT(*) is the usual way to count the number of rows.

Next problem,... GROUP BY age, yet you list name and status. Which name are you expecting for each `age?

But there is another puzzle. I see 3 double quotes ("). Think about it. Consider using single quotes for either the inner need or the outer need.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Hi Rick, Thank you very much for the response. I actually modified the original query for the sake of posting it here. Actual query is correct and runs fine in real DB except in H2. Is it because H2 doesn't support COUNT(status)? How do I chekc for status for being NOT NULL? – Faraz Nov 08 '18 at 20:26
  • what is backtics? – Faraz Nov 08 '18 at 20:29
  • Hi, I added `and status IS NOT NULL` in where condition and modified count(status) with count(*), I am stil seeing the same error. – Faraz Nov 08 '18 at 20:53
  • nevermind I m retarded. There was a rowmapper implementation where count(status) was present. Converted that to count(*) as well. – Faraz Nov 08 '18 at 21:00