topic ORA-00918 column ambiguously defined when querying historic task instances in Alfresco Archive https://connect.hyland.com/t5/alfresco-archive/ora-00918-column-ambiguously-defined-when-querying-historic-task/m-p/154964#M109356 <HTML><HEAD></HEAD><BODY><SPAN>Hello,</SPAN><BR /><BR /><SPAN>I am using Activiti 5.13 with Oracle DB 11g. When I use the REST API to query for a historical task instance given a set of process variable, like this:</SPAN><BR /><BR /><SPAN>POST localhost:9090/activiti-rest/service/query/historic-task-instances</SPAN><BR /><SPAN>{ </SPAN><BR /><SPAN>&nbsp; "includeProcessVariables":true,</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; "processVariables": [</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "name":"requestId",</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "value":44,</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "operation":"equals",</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "type":"long"</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; ]</SPAN><BR /><SPAN>}</SPAN><BR /><BR /><SPAN>I get Error 500 and in the web container logs I see this:</SPAN><BR /><BR /><SPAN>WARNING: Exception or error caught in resource</SPAN><BR /><SPAN>org.apache.ibatis.exceptions.PersistenceException: </SPAN><BR /><SPAN>### Error querying database.&nbsp; Cause: java.sql.SQLSyntaxErrorException: ORA-00918: column ambiguously defined</SPAN><BR /><BR /><SPAN>### The error may exist in org/activiti/db/mapping/entity/HistoricTaskInstance.xml</SPAN><BR /><SPAN>### The error may involve org.activiti.engine.impl.persistence.entity.HistoricTaskInstanceEntity.selectHistoricTaskInstancesWithVariablesByQueryCriteria-Inline</SPAN><BR /><SPAN>### The error occurred while setting parameters</SPAN><BR /><SPAN>### SQL: select * from ( select a.*, ROWNUM rnum from (&nbsp;&nbsp;&nbsp;&nbsp; select distinct RES.*,&nbsp;&nbsp;&nbsp;&nbsp; VAR.ID_ as VAR_ID_, VAR.NAME_ as VAR_NAME_, VAR.VAR_TYPE_ as VAR_TYPE_, VAR.REV_ as VAR_REV_,&nbsp;&nbsp;&nbsp;&nbsp; VAR.PROC_INST_ID_ as VAR_PROC_INST_ID_, VAR.EXECUTION_ID_ as VAR_EXECUTION_ID_, VAR.TASK_ID_ as VAR_TASK_ID_,&nbsp;&nbsp;&nbsp;&nbsp; VAR.BYTEARRAY_ID_ as VAR_BYTEARRAY_ID_, VAR.DOUBLE_ as VAR_DOUBLE_,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VAR.TEXT_ as VAR_TEXT_, VAR.TEXT2_ as VAR_TEXT2_, VAR.LONG_ as VAR_LONG_&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from ACT_HI_TASKINST RES&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; left outer join ACT_HI_VARINST VAR ON RES.PROC_INST_ID_ = VAR.EXECUTION_ID_ and VAR.TASK_ID_ is null&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; inner join ACT_HI_VARINST A0 on RES.PROC_INST_ID_ = A0.PROC_INST_ID_&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE&nbsp; A0.TASK_ID_ is null&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and A0.NAME_= ?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and A0.VAR_TYPE_ = ?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and A0.LONG_&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; order by ID_ asc&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) a where ROWNUM &lt; ?) where rnum&nbsp; &gt;= ?</SPAN><BR /><SPAN>### Cause: java.sql.SQLSyntaxErrorException: ORA-00918: column ambiguously defined</SPAN><BR /><BR /><SPAN>&nbsp;&nbsp;&nbsp;at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:107)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:98)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;at org.activiti.engine.impl.db.DbSqlSession.selectListWithRawParameter(DbSqlSession.java:284)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:275)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:265)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;at org.activiti.engine.impl.persistence.entity.HistoricTaskInstanceEntityManager.findHistoricTaskInstancesAndVariablesByQueryCriteria(HistoricTaskInstanceEntityManager.java:63)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;at org.activiti.engine.impl.HistoricTaskInstanceQueryImpl.executeList(HistoricTaskInstanceQueryImpl.java:89)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;at org.activiti.engine.impl.AbstractQuery.execute(AbstractQuery.java:143)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:61)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:31)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;at org.activiti.engine.impl.AbstractQuery.listPage(AbstractQuery.java:124)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;at org.activiti.rest.api.AbstractPaginateList.paginateList(AbstractPaginateList.java:75)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;at org.activiti.rest.api.history.HistoricTaskInstanceBaseResource.getQueryResponse(HistoricTaskInstanceBaseResource.java:167)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;at org.activiti.rest.api.history.HistoricTaskInstanceQueryResource.queryProcessInstances(HistoricTaskInstanceQueryResource.java:28)</SPAN><BR /><BR /><SPAN>The only column name that may be ambiguous in this case is ROWNUM, which I guess is used to page results. No error shows up if I don't use process variables as a query parameter.</SPAN><BR /><BR /><SPAN>Can you help?</SPAN><BR /><BR /><SPAN>Francesco</SPAN></BODY></HTML> Wed, 04 Sep 2013 21:31:41 GMT fmeschia 2013-09-04T21:31:41Z ORA-00918 column ambiguously defined when querying historic task instances https://connect.hyland.com/t5/alfresco-archive/ora-00918-column-ambiguously-defined-when-querying-historic-task/m-p/154964#M109356 Hello,I am using Activiti 5.13 with Oracle DB 11g. When I use the REST API to query for a historical task instance given a set of process variable, like this<IMG id="smileytongue" class="emoticon emoticon-smileytongue" src="https://migration33.stage.lithium.com/i/smilies/16x16_smiley-tongue.png" alt="Smiley Tongue" title="Smiley Tongue" />OST localhost:9090/activiti-rest/service/query/historic-task-instances{ &nbsp; "includeProcessVariables":true,&nbsp;&nbsp;&nbsp; "processVariables": [&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp; Wed, 04 Sep 2013 21:31:41 GMT https://connect.hyland.com/t5/alfresco-archive/ora-00918-column-ambiguously-defined-when-querying-historic-task/m-p/154964#M109356 fmeschia 2013-09-04T21:31:41Z Re: ORA-00918 column ambiguously defined when querying historic task instances https://connect.hyland.com/t5/alfresco-archive/ora-00918-column-ambiguously-defined-when-querying-historic-task/m-p/154965#M109357 <HTML><HEAD></HEAD><BODY><SPAN>We ran into this issue some days ago as well. We fixed it on the github master version already.</SPAN><BR /><BR /><SPAN>Best regards,</SPAN></BODY></HTML> Fri, 06 Sep 2013 17:33:51 GMT https://connect.hyland.com/t5/alfresco-archive/ora-00918-column-ambiguously-defined-when-querying-historic-task/m-p/154965#M109357 trademak 2013-09-06T17:33:51Z Re: ORA-00918 column ambiguously defined when querying historic task instances https://connect.hyland.com/t5/alfresco-archive/ora-00918-column-ambiguously-defined-when-querying-historic-task/m-p/154966#M109358 <HTML><HEAD></HEAD><BODY><SPAN>Thanks! for the book, too… <img id="smileyhappy" class="emoticon emoticon-smileyhappy" src="https://connect.hyland.com/i/smilies/16x16_smiley-happy.png" alt="Smiley Happy" title="Smiley Happy" /></SPAN></BODY></HTML> Sat, 07 Sep 2013 00:24:34 GMT https://connect.hyland.com/t5/alfresco-archive/ora-00918-column-ambiguously-defined-when-querying-historic-task/m-p/154966#M109358 fmeschia 2013-09-07T00:24:34Z