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

book

Article ID: 6604

calendar_today

Updated On:

Products

SUPPORT AUTOMATION- SERVER CA Service Desk Manager - Unified Self Service KNOWLEDGE TOOLS CA Service Management - Asset Portfolio Management CA Service Management - Service Desk Manager

Issue/Introduction

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)

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.

Environment

CA Service Desk Manager 14.1 and 17.0CA Service Management 14.1 and 17.0JasperSoft Studio 6.3

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://comm.support.ca.com/kb/Cant-run-a-query-when-Assignee-Combo-Name-is-placed-in-the-query-filter-when-we-create-a-Web-Intelligence-document/KB000052433