LOVS Management ERROR Check your syntax.

Question

Hi Everyone,

I'm new on Knowage. I'm interesting this new platforms and currently doing some setups base from previous SpagoBI V5.2 Demo version features.
I try to setup the LOVS management base on the previous SpagoBI 5.2 DEMO and prompt error as below:

But i can query it in MySQ as below:

Select distinct o.BIOBJ_ID AS ID, o.NAME AS DOC_NAME, o.DESCR, o.BIOBJ_TYPE_CD AS DOC_TYPE, o.LABEL
From SBI_OBJECTS o, SBI_AUDIT a
where o.NAME=a.DOC_NAME
order by o.NAME

 

Thank you and appreciate your help.

Best Regards,

Herman
Indonesia

Comments

Hi,

   are you setting the right dialect for the dwh?

Please, check if there are some errors on logs or js console (browser).

Just to try, you could re-write another simple query that reads from the same tables...

---

Hi Giachino,

Yes, I've configured to MySQLDialect. No error on logs prompt.

I able to do for the same query but without changing the column name. The error prompt if you want to change the column name. Please refer to below:

-Success : Query without changing the name column:

Select distinct o.BIOBJ_ID, o.NAME, o.DESCR, o.BIOBJ_TYPE_CD, o.LABEL
From SBI_OBJECTS o, SBI_AUDIT a
where o.NAME=a.DOC_NAME
order by o.NAME

-Error : Query with changing the column name (AS);

Select distinct o.BIOBJ_ID AS ID, o.NAME AS DOC_NAME, o.DESCR, o.BIOBJ_TYPE_CD AS DOC_TYPE, o.LABEL
From SBI_OBJECTS o, SBI_AUDIT a
where o.NAME=a.DOC_NAME
order by o.NAME.

Thank you

Herman

---

Did you try to delete the keyword 'AS' from the original query?

Try with a query like this:

Select distinct o.BIOBJ_ID  ID, o.NAME DOC_NAME, o.DESCR, o.BIOBJ_TYPE_CD DOC_TYPE, o.LABEL
From SBI_OBJECTS o, SBI_AUDIT a
where o.NAME=a.DOC_NAME
order by o.NAME.

---

Hi Giachino,

I've tried the query as your suggest, no luck still got the 'error check your syntax'.

Thank you.

Answer 1

Hi,

   that's really strange. I just create a test lov with all your queries on  mysql dbs (knowage schema) and they work!

So, are you sure that are you using the correct schema / dialect? Maybe are there some hidden strange chars?

The query is very simple so I cannot suppose other causes...

Regards

Comments

Hi Giachino,
 

Yes, I also setup for other query below and it's work.

SELECT distinct VALUE_CD as Type
FROM knowage_ce.sbi_domains
where domain_cd='BIOBJ_TYPE'
union
SELECT null as Type
FROM knowage_ce.sbi_domains
where domain_cd='BIOBJ_TYPE';

I can preview the result in DATASET using below query but still error in LOV:

Select distinct o.BIOBJ_ID as ID, o.NAME, o.DESCR as DESCRIPTIONS, o.BIOBJ_TYPE_CD, o.LABEL
From SBI_OBJECTS o, SBI_AUDIT a
where o.NAME=a.DOC_NAME
order by o.NAME;

 Here's the error log in Knowage.log

[http-apr-8080-exec-3] 08 Feb 2018 19:30:24,719 ERROR it.eng.spagobi.api.v2.LovResource.preview:312 - Cannot get values from query

[http-apr-8080-exec-3] 08 Feb 2018 19:30:24,735 ERROR it.eng.spagobi.api.v2.LovResource.preview:347 - Error reading body

it.eng.spagobi.utilities.exceptions.SpagoBIRuntimeException: Cannot get values from query

at it.eng.spagobi.api.v2.LovResource.preview(LovResource.java:313)

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

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

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.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2441)

at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2430)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

at java.lang.Thread.run(Thread.java:748)

I'll find some free time to re-install MySQL server and Knowage.

Thank you.
Regards,

Herman