java.sql.SQLSyntaxErrorException: (conn=83) Every derived table must have its own alias

Question

Hi, 
After upgrading my knowage from version 6.2 to version 6.4 basing on the guide, everything is fine except for the dataset. It's the first time that I have encountered this kind of Error. Please refer to the information below and please advise us if you can help. Thank you in advance.

 
Upon Clicking on the Dataset, this Error occurs :

---

ErrorEvery derived table must have its own alias Query is: select * from ( select sbidataset0_.DS_ID as DS1_54_, sbidataset0_.VERSION_NUM as VERSION2_54_, sbidataset0_.ORGANIZATION as ORGANIZA3_54_, sbidataset0_.CATEGORY_ID as CATEGORY4_54_, sbidataset0_.TRANSFORMER_ID as TRANSFOR5_54_, sbidataset0_.SCOPE_ID as SCOPE6_54_, sbidataset0_.ACTIVE as ACTIVE54_, sbidataset0_.NAME as NAME54_, sbidataset0_.DESCR as DESCR54_, sbidataset0_.LABEL as LABEL54_, sbidataset0_.OBJECT_TYPE as OBJECT11_54_, sbidataset0_.PARAMS as PARAMS54_, sbidataset0_.DS_METADATA as DS13_54_, sbidataset0_.PIVOT_COLUMN as PIVOT14_54_, sbidataset0_.PIVOT_ROW as PIVOT15_54_, sbidataset0_.PIVOT_VALUE as PIVOT16_54_, sbidataset0_.NUM_ROWS as NUM17_54_, sbidataset0_.IS_PERSISTED as IS18_54_, sbidataset0_.IS_PERSISTED_HDFS as IS19_54_, sbidataset0_.PERSIST_TABLE_NAME as PERSIST20_54_, sbidataset0_.CONFIGURATION as CONFIGU21_54_, sbidataset0_.OWNER as OWNER54_, sbidataset0_.FEDERATION_ID as FEDERATION23_54_, sbidataset0_.USER_IN as USER24_54_, sbidataset0_.USER_UP as USER25_54_, sbidataset0_.USER_DE as U...


And this is what we found in the logs :

---

[localhost-startStop-2] 23 May 2019 08:29:09,340 ERROR it.eng.spagobi.tools.scheduler.init.RestEventQuartzInitializer.initRestEventsForTenant:99 - Error while initializing scheduler 

it.eng.spagobi.commons.dao.SpagoBIDAOException: An unexpected error occured while inserting trigger[Trigger [name=schedule_rest_events, groupName=null]]

at it.eng.spagobi.tools.scheduler.dao.quartz.QuarzSchedulerDAOImpl.insertTrigger(QuarzSchedulerDAOImpl.java:540)

at it.eng.spagobi.tools.scheduler.init.RestEventQuartzInitializer.initRestEventsForTenant(RestEventQuartzInitializer.java:94)

at it.eng.spagobi.tools.scheduler.init.RestEventQuartzInitializer.init(RestEventQuartzInitializer.java:54

... 43 more

Caused by: java.sql.SQLException: Every derived table must have its own alias

Query is: select * from ( select sbidataset0_.DS_ID as DS1_54_, sbidataset0_.VERSION_NUM as VERSION2_54_, sbidataset0_.ORGANIZATION as ORGANIZA3_54_, sbidataset0_.CATEGORY_ID as CATEGORY4_54_, sbidataset0_.TRANSFORMER_ID as TRANSFOR5_54_, sbidataset0_.SCOPE_ID as SCOPE6_54_, sbidataset0_.ACTIVE as ACTIVE54_, sbidataset0_.NAME as NAME54_, sbidataset0_.DESCR as DESCR54_, sbidataset0_.LABEL as LABEL54_, sbidataset0_.OBJECT_TYPE as OBJECT11_54_, sbidataset0_.PARAMS as PARAMS54_, sbidataset0_.DS_METADATA as DS13_54_, sbidataset0_.PIVOT_COLUMN as PIVOT14_54_, sbidataset0_.PIVOT_ROW as PIVOT15_54_, sbidataset0_.PIVOT_VALUE as PIVOT16_54_, sbidataset0_.NUM_ROWS as NUM17_54_, sbidataset0_.IS_PERSISTED as IS18_54_, sbidataset0_.IS_PERSISTED_HDFS as IS19_54_, sbidataset0_.PERSIST_TABLE_NAME as PERSIST20_54_, sbidataset0_.CONFIGURATION as CONFIGU21_54_, sbidataset0_.OWNER as OWNER54_, sbidataset0_.FEDERATION_ID as FEDERATION23_54_, sbidataset0_.USER_IN as USER24_54_, sbidataset0_.USER_UP as USER25_54_, sbidataset0_.USER_DE as U...

at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:153)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:254)

at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:209)

... 50 more

[http-bio-8080-exec-8] 23 May 2019 08:32:32,316 ERROR org.hibernate.util.JDBCExceptionReporter.logExceptions:234 - (conn=49) Every derived table must have its own alias

[http-bio-8080-exec-8] 23 May 2019 08:32:32,316 ERROR org.hibernate.util.JDBCExceptionReporter.logExceptions:234 - (conn=49) Every derived table must have its own alias

[http-bio-8080-exec-8] 23 May 2019 08:32:32,317 ERROR it.eng.spagobi.rest.interceptors.RestExceptionMapper.toResponse:59 - Catched service error: 

it.eng.spagobi.utilities.exceptions.SpagoBIServiceException: An unexpected error occured while executing service

at it.eng.spagobi.api.DataSetResource.getDataSetsPaginationOption(DataSetResource.java:306)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:140)

at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTarget(ResourceMethodInvoker.java:294)

at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:248)

at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:235)

at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:398)

at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:205)

at org.jboss.resteasy.plugins.server.servlet.ServletContainerDispatcher.service(ServletContainerDispatcher.java:228)

at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:56)

at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:51)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)

at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)

at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:409)

at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1044)

at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)

at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:315)

at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.lang.Thread.run(Unknown Source)

Comments

Hi samsiervo,

Have you found the solution to this error ? I'm facing same issue with knowage 6.4.0.

Thanks for you support.

Patrick

---

I have the same issue by upgrading knowage 6.3.3 to 6.4.2. 

In the DataSet menu, there is the error message displayed "ERROR. Every derived table must have its own alias.".  

I clicked twice on OK button then I tried to create and save the dataset. The success message is displayed but I don't see it in the list of dataset. When I click on the noname of the dataset , there is an error message displayed: "ERROR. RESTEASY003870: Unable to extract parameter from http request: javax.ws.rs.PathParam("id") value is {1} for public java.lang.String it.eng.spagobi.api.DataSetResource.geOlderVersionsForDataset(int)". 

Regards

  

Answer 1

hello,

Finally, after trying with the 7.0.0-RC release where I face the same issue, I've downgrade the Knowage to the 6.4.1 release and the issue has disappear: Woth Knowage 6.4.1, no more error pop-up when creating a DataSet.

So the issue I face looks to be a regression introduced in Knowage 6.4.2 release.

Hoping this help to debug the soft.

Patrick

Comments

Hi Patrick, thank you for your response. Yes it was fixed by the latest version :)

Answer 2

I had the same problem and I resolved it changing the hibernate dialect

(from <property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>

to <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>).

The files to modify are

knowage/webapps/knowage/WEB-INF/classes/hibernate.cfg.xml
knowage/webapps/knowagecockpitengine/WEB-INF/classes/hibernate.cfg.xml
knowage/webapps/knowagedataminingengine/WEB-INF/classes/hibernate.cfg.xml
knowage/webapps/knowagegeoreportengine/WEB-INF/classes/hibernate.cfg.xml
knowage/webapps/knowagekpiengine/WEB-INF/classes/hibernate.cfg.xml
knowage/webapps/knowagemeta/WEB-INF/classes/hibernate.cfg.xml
knowage/webapps/knowagesvgviewerengine/WEB-INF/classes/hibernate.cfg.xml

Have a nice day,

Gianluca

Comments

Thank you for this, sorry it's been a while before I got a chance to reply. Yes , I also did this when there's no updated version yet and it worked.