CA Service Management

I'm new to JasperReports development, so I'm trying to learn through OOTB reports that is packaged within Jasperreports Server 6.2. I was able to configure the SDM adapter in Jaspersoft Studio 6.3 to communicate with Jasperreports server 6.2. I ran an OOTB report which seems to get some results and when tried to validate the query in dataset and query window, it failed with below errors. Please suggest on how I could get rid of this error to successfully validate the query, so I could reuse part of this query along with fields and parameters for building custom reports.

See below for the error output:

net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query:

SELECT
cr.group_name,
cr.ref_num,
cr.open_date,
cr.priority_symbol,
cr.status_symbol,
cr.request_area_symbol,
cr.active,
cr.web_url,
(PdmToday() - cr.open_date)/(7*24*3600),
(PdmToday() - cr.open_date)/(24*3600),
(PdmToday() - cr.open_date)/3600,

(case when ((PdmToday() - cr.open_date)/(7*24*3600)) <2 then '1'
when ((PdmToday() - cr.open_date)/(7*24*3600)) >=2 AND ((PdmToday() - cr.open_date)/(7*24*3600)) <3 then '2'
when ((PdmToday() - cr.open_date)/(7*24*3600)) >=3 AND ((PdmToday() - cr.open_date)/(7*24*3600)) <4 then '3'
when ((PdmToday() - cr.open_date)/(7*24*3600)) >=4 AND ((PdmToday() - cr.open_date)/(7*24*3600)) <5 then '4'
when ((PdmToday() - cr.open_date)/(7*24*3600)) >=5 AND ((PdmToday() - cr.open_date)/(7*24*3600)) <8 then '5'
when ((PdmToday() - cr.open_date)/(7*24*3600)) >=8 then '6'
ELSE '' end ) as WeeksOpened,

case when ((PdmToday() - cr.open_date)/(7*24*3600)) < 2 THEN 1 ELSE 0 END AS "1Week",
case when ((PdmToday() - cr.open_date)/(7*24*3600)) >= 2 AND ((PdmToday() - cr.open_date)/(7*24*3600)) < 3 THEN 1 ELSE 0 END AS "2Weeks",
case when ((PdmToday() - cr.open_date)/(7*24*3600)) >= 3 AND ((PdmToday() - cr.open_date)/(7*24*3600)) < 4 THEN 1 ELSE 0 END AS "3Weeks",
case when ((PdmToday() - cr.open_date)/(7*24*3600)) >= 4 AND ((PdmToday() - cr.open_date)/(7*24*3600)) < 5 THEN 1 ELSE 0 END AS "4Weeks",
case when ((PdmToday() - cr.open_date)/(7*24*3600)) >= 5 AND ((PdmToday() - cr.open_date)/(7*24*3600)) < 8 THEN 1 ELSE 0 END AS "30-60Days",
case when ((PdmToday() - cr.open_date)/(7*24*3600)) >= 8 THEN 1 ELSE 0 END AS "Over60Days"

FROM cr
WHERE cr.active=1 AND cr.group_name IS NOT NULL AND cr.type = 'R'
AND (cr.group_name in or '*' in )
ORDER BY cr.priority_symbol ASC, cr.open_date ASC, cr.group_name ASC

at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:528)

at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:297)

at com.jaspersoft.studio.data.jdbc.JDBCFieldsProvider.getFields(JDBCFieldsProvider.java:79)

at com.jaspersoft.studio.data.jdbc.JDBCDataAdapterDescriptor.getFields(JDBCDataAdapterDescriptor.java:75)

at com.jaspersoft.studio.property.dataset.dialog.DataQueryAdapters.doGetFields(DataQueryAdapters.java:396)

at com.jaspersoft.studio.data.designer.AQueryDesignerContainer$1.run(AQueryDesignerContainer.java:53)

at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:119)

Caused by: java.sql.SQLException: [DataDirect][OpenAccess SDK JDBC Driver][OpenAccess SDK SQL Engine]syntax error, unexpected OR, expecting '(' at group_name ... or

at com.ddtek.openaccess.ssp.Diagnostic.toSQLException(Unknown Source)

at com.ddtek.openaccess.ssp.Chain.cnvDiagnostics(Unknown Source)

at com.ddtek.openaccess.ssp.Chain.decodeDiagnostic(Unknown Source)

at com.ddtek.openaccess.ssp.Chain.decodeBody(Unknown Source)

at com.ddtek.openaccess.ssp.Chain.decode(Unknown Source)

at com.ddtek.openaccess.ssp.Chain.send(Unknown Source)

at com.ddtek.openaccess.ctxt.stmt.StatementContext.associate(Unknown Source)

at com.ddtek.jdbc.openaccess.OpenAccessImplStatement.prepare(Unknown Source)

at com.ddtek.jdbc.oabase.BaseImplStatement.prepare(Unknown Source)

at com.ddtek.jdbc.oabase.BasePreparedStatement.prepareSQL(Unknown Source)

at com.ddtek.jdbc.oabase.BaseStatement.preProcessSQL(Unknown Source)

at com.ddtek.jdbc.oabase.BasePreparedStatement.<init>(Unknown Source)

at com.ddtek.jdbc.oabase.BaseConnection.prepareStatement(Unknown Source)

at (message continues in next comment)

Hey Vishnu,

It almost feels like this clause is causing the error:

AND (cr.group_name in or '*' in )

Its missing something and that’s why the error from the backend

_R

Raghu,

Could this be an OOTB Jasper report issue with incomplete query or the report query is incompatible with Jaspersoft Studio 6.3?

-Vishnu

Can you check what version of oaserver you are running?

If it is not 72 then that is most likely the issue.

Actually ignore the oaserver check.  I did some further inviestigation and replicated the issue.  Let me look into it further.

I still wanted to double check and it is 72.

Did some investigation over the weekend and determined how best to preview OOTB report SQL using SQL Preview.

What you need to do is basically copy the SQL into a blank (new) report and remove references to $P! parameters.  

In the example noted in this thread I removed "AND (cr.group_name in $P!{p_group} or '*' in $P!{p_group})" and previewed in a new report without failure.  

The reason you need tp do this is because SQL Preview doesn't parse $P! attributes/parameters and actually executes the report JRXML which in some cases can cause the SQL preview to fail as well. 

Steve,

I'm trying to validate the OOTB report query in the same report and even after removing "AND (cr.group_name in $P!{p_group} or '*' in $P!{p_group})" , the query validation still fails.

That a false negative.    If you hit Read Fields and then do a Data Preview/Refresh Preview Data  it will return results. 

I'm not sure of your studio release but with studio 6.3, I see bunch of errors with Preview Data after removing the parameter clause.

Copy SQL to brand new blank report.    Report should only include the SQL code and nothing else.  

Data Preview is trying to execute Report Code which is why it is failing.  

I've attached an example.

Your workaround helped to validate the OOTB report query, Thank You!

Good to hear.  In theory it should work from the original report but there are some shortcomings with SQL Preview that requires this simple workaround.

Cheers!