SQL Exception when selecting event data items in pivot tables

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)

Hi Jose,

this is caused by the “None” aggregation type. It needs to be set in order to have the event analytics work properly.

I have reported the maintenance app issue here:

https://github.com/dhis2/maintenance-app/issues/284

We will fix. If it is urgent you can set the aggregation type through the API or in database.

best regards,

Lars

···

On Tue, Nov 8, 2016 at 6:24 AM, Jose Garcia Muñoz josemp10@gmail.com wrote:

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)


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

lars@dhis2.org

http://www.dhis2.org

This issue was in fact already reported here:

https://github.com/dhis2/maintenance-app/issues/278

Lars

···

On Thu, Nov 17, 2016 at 5:03 PM, Lars Helge Øverland lars@dhis2.org wrote:

Hi Jose,

this is caused by the “None” aggregation type. It needs to be set in order to have the event analytics work properly.

I have reported the maintenance app issue here:

https://github.com/dhis2/maintenance-app/issues/284

We will fix. If it is urgent you can set the aggregation type through the API or in database.

best regards,

Lars

On Tue, Nov 8, 2016 at 6:24 AM, Jose Garcia Muñoz josemp10@gmail.com wrote:

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)


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

lars@dhis2.org

http://www.dhis2.org

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

lars@dhis2.org

http://www.dhis2.org