Hello,
I did a little debugging and figure that this might be because the analytics query is failing with following error.
StatementCallback; bad SQL grammar [select “de”,“weekly”,“uidlevel3”,“a99ecb2c5c3”, sum(value) as value from analytics_2014 where “de” in (‘a0ea1b1d113’) and “weekly” in (‘2014W2’) and “uidlevel3” in (‘a42405657c4’) and “a99ecb2c5c3” in (‘abacfb5842a’, ‘a5dd89a503f’) and ( (uidlevel1 = ‘a2cf79e8f13’ and approvallevel <= 999) ) group by “de”,“weekly”,“uidlevel3”,“a99ecb2c5c3”]; nested exception is org.postgresql.util.PSQLException: ERROR: column “a99ecb2c5c3” does not exist
Position: 34
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:456)
at org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:501)
at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getKeyValueMap(JdbcAnalyticsManager.java:366)
at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getAggregatedDataValues(JdbcAnalyticsManager.java:140)
at sun.reflect.GeneratedMethodAccessor876.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.aop.interceptor.AsyncExecutionInterceptor$1.call(AsyncExecutionInterceptor.java:95)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ERROR: column “a99ecb2c5c3” does not exist
Position: 34
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:283)
at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:35)
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:441)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
… 13 more
- INFO 2014-09-11 12:05:20,742 Query failed, likely because the requested analytics table does not exist (JdbcAnalyticsManager.java [SimpleAsyncTaskExecutor-921])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select “de”,“weekly”,“uidlevel3”,“a99ecb2c5c3”, sum(value) as value from analytics_2014 where “de” in (‘aee83d48c4c’) and “weekly” in (‘2014W2’) and “uidlevel3” in (‘a42405657c4’) and “a99ecb2c5c3” in (‘abacfb5842a’, ‘a5dd89a503f’) and ( (uidlevel1 = ‘a2cf79e8f13’ and approvallevel <= 999) ) group by “de”,“weekly”,“uidlevel3”,“a99ecb2c5c3”]; nested exception is org.postgresql.util.PSQLException: ERROR: column “a99ecb2c5c3” does not exist
Position: 34
I have checked the database, and the table analytics_2014 exists.
I think this is because the generate sql seems wrong.
“uidlevel3” in (‘a42405657c4’) and “a99ecb2c5c3”
should this be “uidlevel3” in (‘a42405657c4’, “a99ecb2c5c3”)? or something else?
Are you guys aware of this issue and if it’s fixed in trunk, what revision?
We’re very close to release, so cannot afford to merge with trunk. Any help pointing out the fix/ revision number will be of great help
Thanks in advance
Kiran.
···
On Wed, Sep 10, 2014 at 1:23 PM, Mahendra Kariya mahendra.kariya@thoughtworks.com wrote:
Hi Lars/Morten,
It would be really helpful if you guys could point us to where we can look for if something is going wrong.
On Tue, Sep 9, 2014 at 10:59 AM, Kiran Prakash kprakash@thoughtworks.com wrote:
Hello Lars,
any insight into this? this is holding up one of our stories. really appreciate if you can have a look in
thanks
Kiran
On Mon, Sep 8, 2014 at 10:30 PM, Mahendra Kariya mahendra.kariya@thoughtworks.com wrote:
Hi Lars,
Even after the “Skip category total in reports” property is set to false for category combinations, the data we are looking for is not coming up. Is there something we are missing?
On Tue, Sep 2, 2014 at 12:34 PM, Lars Helge Øverland larshelge@gmail.com wrote:
Hi Kiran,
please check that the “Skip category total in reports” property is set to false for the relevant category combination.
regards,
Lars
Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp
On Tue, Sep 2, 2014 at 9:18 AM, Kiran Prakash kprakash@thoughtworks.com wrote:
sorry. missed the screen shot last time:
On Tue, Sep 2, 2014 at 12:19 PM, Kiran Prakash kprakash@thoughtworks.com wrote:
Hello All,
We are running on 2.17 Snapshot (not in sync with trunk , some intermediate revision ). And we are facing this issue while pulling Dataset reports.
We are not seeing the numbesr for the breakdown of the category options, but only for category option combos.
I’ve done an analytics export and things went fine.
Let me know if you have any idea why this might be happening.
Please find the screen shot:
Regards,
Kiran.