dhis2-devs team mailing list archive

["jason.p.pickering" <1100706@xxxxxxxxxxxxxxxxxx>]

Hi Hieu,
Thanks for taking a look at this.

Will this work for something like

SELECT de.*, ds.name FROM dataelement de
INNER JOIN datasetmembers dsm on de.dataelementid = dsm.dataelementid
INNER JOIN dataset ds on dsm.datasetid = ds.datasetid ?

Best regards,
Jason


On Mon, Jan 28, 2013 at 12:59 PM, Hieu Duy Dang
<hieu.hispvietnam@xxxxxxxxx>wrote:

> ** Changed in: dhis2
>        Status: New => Fix Committed
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/1100706
>
> Title:
>   sql-view-duplicate-column-names
>
> Status in DHIS 2 - District Health Information Software:
>   Fix Committed
>
> Bug description:
>   When creating a view like..
>
>   SELECT woreda.name, health_post.name FROM _orgunitstructure ous INNER
>   JOIN organisationunit woreda on ous.idlevel4 =
>   woreda.organisationunitid INNER JOIN organisationunit health_post on
>   ous.idlevel6 = health_post.organisationunitid;
>
>   the name column is duplicated. Although this is valid SQL, it is not
>   valid to materialize a table.
>
>   SELECT woreda.name as woreda, health_post.name as health_post  FROM
>   _orgunitstructure ous INNER JOIN organisationunit woreda on
>   ous.idlevel4 = woreda.organisationunitid INNER JOIN organisationunit
>   health_post on ous.idlevel6 = health_post.organisationunitid;
>
>   The SQL View function will allow you to save the first view, but will
>   throw an error silently in the logs (shown below) when the view is
>   attempted to be materialized.
>
>
>   Caused by: org.postgresql.util.PSQLException: ERROR: column "name"
> specified more than once
>           at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
>           at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
>           at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
>           at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
>           at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
>           at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:366)
>           at
> com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:1006)
>           at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>           at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>           at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>           at java.lang.reflect.Method.invoke(Method.java:616)
>           at
> org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
>           ... 112 more
>
>   This is a sort of borderline bug, but at the very least, we need to
>   report that something went wrong. This error is just swallowed and the
>   user never sees that an error occurred.
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/dhis2/+bug/1100706/+subscriptions
>

-- 
You received this bug notification because you are a member of DHIS 2
developers, which is subscribed to DHIS.
https://bugs.launchpad.net/bugs/1100706

Title:
  sql-view-duplicate-column-names

Status in DHIS 2 - District Health Information Software:
  Fix Committed

Bug description:
  When creating a view like..

  SELECT woreda.name, health_post.name FROM _orgunitstructure ous INNER
  JOIN organisationunit woreda on ous.idlevel4 =
  woreda.organisationunitid INNER JOIN organisationunit health_post on
  ous.idlevel6 = health_post.organisationunitid;

  the name column is duplicated. Although this is valid SQL, it is not
  valid to materialize a table.

  SELECT woreda.name as woreda, health_post.name as health_post  FROM
  _orgunitstructure ous INNER JOIN organisationunit woreda on
  ous.idlevel4 = woreda.organisationunitid INNER JOIN organisationunit
  health_post on ous.idlevel6 = health_post.organisationunitid;

  The SQL View function will allow you to save the first view, but will
  throw an error silently in the logs (shown below) when the view is
  attempted to be materialized.

  
  Caused by: org.postgresql.util.PSQLException: ERROR: column "name" specified more than once
          at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
          at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
          at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
          at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
          at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
          at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:366)
          at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:1006)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
          at java.lang.reflect.Method.invoke(Method.java:616)
          at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
          ... 112 more

  This is a sort of borderline bug, but at the very least, we need to
  report that something went wrong. This error is just swallowed and the
  user never sees that an error occurred.

To manage notifications about this bug go to:
https://bugs.launchpad.net/dhis2/+bug/1100706/+subscriptions