dhis2-devs team mailing list archive

[Kiran Prakash <kprakash@xxxxxxxxxxxxxxxx>]

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@xxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxx>
> 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@xxxxxxxxxxxxxxxx> 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@xxxxxxxxx> wrote:
>>>
>>>> Hi Kiran,
>>>>
>>>> please check that the "Skip category total in reports" property is set
>>>> to false for the relevant category combination.
>>>>
>>>> regards,
>>>>
>>>> Lars
>>>>
>>>>
>>>>
>>>> On Tue, Sep 2, 2014 at 9:18 AM, Kiran Prakash <
>>>> kprakash@xxxxxxxxxxxxxxxx> wrote:
>>>>
>>>>> sorry. missed the screen shot last time:
>>>>>
>>>>>
>>>>> On Tue, Sep 2, 2014 at 12:19 PM, Kiran Prakash <
>>>>> kprakash@xxxxxxxxxxxxxxxx> 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.
>>>>>>
>>>>>
>>>>>
>>>>
>>>> _______________________________________________
>>>> Mailing list: https://launchpad.net/~dhis2-users
>>>> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
>>>> Unsubscribe : https://launchpad.net/~dhis2-users
>>>> More help   : https://help.launchpad.net/ListHelp
>>>>
>>>>
>>>
>>
>