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)
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.
CA Service Desk Manager 14.1 and 17.0CA Service Management 14.1 and 17.0JasperSoft Studio 6.3
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
Cannot run a query when 'Assignee Combo Name' is placed in the query filter when we create a Web Intelligence document.