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.
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.