Hi devs,
each time I try to do some calculation with event data items (numeric/integer/… tracker dataelements), I’m getting the following SQL exception (attached at the bottom of them email). The SQL is:
select none(“DvTQcjDWH3M”) as value,“monthly” from analytics_event_2016_lyyizex7izm where Monthly in (‘201601’, ‘201602’, ‘201603’, ‘201604’, ‘201605’, ‘201606’, ‘201607’, ‘201608’, ‘201609’, ‘201610’) and (uidlevel1 = ‘FvUGp8I75zV’ ) group by “monthly” limit 100001
The table ‘analytics_event_2016_lyyizex7izm’ exists in the database, so I believe this is happening becuase the agg operator should be ‘sum’ instead of ‘none’ (otherwise we would need to group also by the “DvTQcjDWH3M” column). I check the dataelement definition and in fact the agg type is ‘none’. However in 2.25, the interface does not allow us to create tracker dataelements with an aggregation type different than ‘none’…
Would this be related to the issue we are having? Am I missing something? It is strange because it is working in the Sierra Leone demo, but I don’t know if this is because the datelements has been upgraded from the previous version, while I’m creating them directly in 2.25
Many thanks
Jose
- INFO 2016-11-08 05:45:59,032 Query failed, likely because the requested analytics table does not exist (JdbcEventAnalyticsManager.java [http-apr-8080-exec-4])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select none(“DvTQcjDWH3M”) as value,“monthly” from analytics_event_2016_lyyizex7izm where Monthly in (‘201601’, ‘201602’, ‘201603’, ‘201604’, ‘201605’, ‘201606’, ‘201607’, ‘201608’, ‘201609’, ‘201610’) and (uidlevel1 = ‘FvUGp8I75zV’ ) group by “monthly” limit 100001]; nested exception is org.postgresql.util.PSQLException: ERROR: error de sintaxis en o cerca de ½(╗
Position: 12
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)