Query work, but no in Jaspersoft

[robert.hassel]

Hi, 

Can anyone please tell me why this query doesn't work for me in jaspersoft?

SELECT bptech.externalId as Partner,COUNT(s.id) as Pocet, sas.Servis , (100/Pocet*sas.Servis) as Percento

from ServiceCall s, Activity a, ServiceAssignment sa, Person p, BusinessPartner bptech,

(SELECT bpteche.externalId as Partner2, COUNT(se.id) as Servis

FROM ServiceCall se ,Activity ae, ServiceAssignment sae, Person pe, BusinessPartner bpteche

WHERE se.typeName = 'Servis' AND

ae.object.objectId = se.id AND

sae.object.objectId = se.id AND

pe.id=sae.technician AND

bpteche.id = pe.businessPartner AND

(ae.udf.z_f_ac_zmenaterminu LIKE '%4.%' OR ae.udf.z_f_ac_zmenaterminu LIKE '%5.%')

GROUP BY bpteche.externalId) sas

WHERE

a.object.objectId = s.id AND

sa.object.objectId = s.id AND

p.id=sa.technician AND

bptech.id = p.businessPartner AND

sas.Partner2 = bptech.externalId AND

s.typeName = 'Servis'

GROUP BY Partner, Servis

[bigalex]

Would you be able to provide the error you see in  Jasper? I am just guessing here that Jasper doesn’t like the query line with .object. ( ae.object.objectId) , the error may provide more info on why the query doesn’t work in Jasper.

[robert.hassel]

The error is very general: 

CA-158 - The query was cancelled because it takes more then 30 seconds to execuce. 

In other system this query is finis in 2 seconds... 

I think there is problem with two SELECT in one query, but I dont know how can write this query without two SELECT. 

ae.object.objectId works in another query in Jaspersoft. 

Thank you! 

[bigalex]

Would you be able to provide the full TEXT error ?

Also,check to make sure the sas.Servis and Pocet are not Zero; otherwise, it will be an endless loop in your formula (100/Pocet*sas.Servis). Use NULLIF function - NULLIF(expression1, expression2) to avoid division by zero.

[robert.hassel]

 

Thank you.  Bellow is full text of error. Pocet and sas.Servis are not zero.

com.coresuite.reportdesigner.exception.ReportDesignerException: {"error":"CA-158","message":"CA-158: The query was cancelled because it takes more than 30 seconds to execute.","values":[],"id":"bc9d96e87c1244668c9eef21d60cd7e3"}
    at com.coresuite.reportdesigner.client.HttpClient.call(HttpClient.java:153)
    at com.coresuite.reportdesigner.client.HttpClient.get(HttpClient.java:54)
    at com.coresuite.reportdesigner.client.CloudClient.readQuery(CloudClient.java:178)
    at com.coresuite.reportdesigner.query.CoresuiteFieldsProvider.getFields(CoresuiteFieldsProvider.java:74)
    at com.jaspersoft.studio.data.coresuite.CoresuiteDataAdapterDescriptor.getFields(CoresuiteDataAdapterDescriptor.java:57)
    at com.jaspersoft.studio.property.dataset.dialog.DataQueryAdapters.doGetFields(DataQueryAdapters.java:525)
    at com.jaspersoft.studio.data.designer.AQueryDesignerContainer$1.run(AQueryDesignerContainer.java:49)
    at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:122)
 

[bigalex]

It looks like the CA-158 error (SAP) is the same as the HTTP 408 error. Check the article: What Is a 408 Request Timeout and How to Fix It (https://www.exai.com/blog/408-request-timeout-error). I hope this helps.

[robert.hassel]

Hi, 

Yes, I read what mean CA-158, but doesn't help me. I think, that this error will be right so other select don't work also with this connection to DB.  Every other select with the same connection DB works good, but this one not.

Robo