AnsweredAssumed Answered

OOTB JasperReports query issue in Studio 6.3

Question asked by vchinni on Mar 24, 2017
Latest reply on Mar 28, 2017 by vchinni

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)

Outcomes