"AHD12019:Cannot use sync_fetch for a query" error when running a query in JasperSoft Studio

Document ID : KB000006604
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

The following error appears in JasperSoft Studio when running a query which is filtered by the 'combo_name' field:

net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query:
<Query using combo_name in where clause>
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:528)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:297)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1129)
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:696)
at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:437)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:548)
at net.sf.jasperreports.engine.fill.JRFiller.fill(JRFiller.java:163)
at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:474)
at com.jaspersoft.studio.data.reader.DatasetReader.fillReport(DatasetReader.java:242)
at com.jaspersoft.studio.data.reader.DatasetReader.start(DatasetReader.java:301)
at com.jaspersoft.studio.property.dataset.dialog.DataPreviewTable$5.run(DataPreviewTable.java:256)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55)

Caused by: java.sql.SQLException: [DataDirect][OpenAccess SDK JDBC Driver][OpenAccess SDK SQL Engine]AHD12019:Cannot use sync_fetch for a query referencing column <column name> in a JOIN clause at <column name> (AHD12014:Query references <object_name>.combo_name outside of selection list)

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 com.ddtek.jdbc.oabase.BaseConnection.prepareStatement(Unknown Source)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:384)

Environment:
CA Service Desk Manager 14.1 and 17.0CA Service Management 14.1 and 17.0JasperSoft Studio 6.3
Cause:

In a CA Service Desk Manager MAJIC file, the definition has the attribute 'DERIVED' for the 'combo_name' field and cannot be used to filter in a query. This behavior is by design.

Resolution:

1.  Log into JasperSoft Studio and edit the query

2.  Ensure the query being used does not filter by combo name.

Example:

The following query does have the issue:

SELECT
casd.pcat.sym,
cnt2.combo_name
FROM
casd.pcat
inner join casd.cnt cnt2 On (casd.pcat.zcat_grpsol = cnt2.id)
where cnt2.combo_name = 'Level 1'

 The following query does NOT have the issue:

SELECT
casd.pcat.sym,
cnt2.last_name,
cnt2.combo_name
FROM
casd.pcat
inner join casd.cnt cnt2 On (casd.pcat.zcat_grpsol3 = cnt2.id)
where cnt2.last_name = 'Level 1'

3.  Run the query via JasperSoft Studio

Additional Information:

Cannot run a query when 'Assignee Combo Name' is placed in the query filter when we create a Web Intelligence document.

https://www.ca.com/us/services-support/ca-support/ca-support-online/knowledge-base-articles.tec509848.html