The parameter for cr.assignee_combo_name or assignee_department_name are resulting in compiling errors. Is there a workaround to prompt users for entering assignee combo name and department name in a Jaspersoft report?
SELECT
cr.assignee_combo_name,
cr.assignee_department_name,
cr.group_name,
cr.ref_num,
cr.open_date,
cr.priority_symbol,
cr.status_symbol,
cr.request_area_symbol,
cr.active,
cr.assignee_combo_name AS ANALYST
FROM cr
WHERE cr.active=1 AND cr.type = 'R'
AND (cr.assignee_combo_name IS NOT NULL)
--AND (cr.assignee_combo_name in $P!{p_analyst})
--AND ( cr.assignee_combo_name in $P!{p_analyst} or '*' in $P!{p_analyst})
--AND ( cr.assignee_department_name in $P!{p_department} or '*' in $P!{p_department})
ORDER BY ANALYST ASC
parameter: p_analyst
Class: java.lang.String
parameter: p_deparment
Class: java.lang.String
Compiling Errors:
Caused by: java.sql.SQLException: [DataDirect][OpenAccess SDK JDBC Driver][OpenAccess SDK SQL Engine]AHD12002:Error in QueryHandler ServiceDesk-1868998639-1 in method fetch_response: AHD03053:Bad where clause: invalid constraint. Unable to resolve assignee.combo_name.
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.execute(Unknown Source)
at com.ddtek.jdbc.openaccess.OpenAccessImplStatement.execute(Unknown Source)
at com.ddtek.jdbc.oabase.BaseStatement.commonExecute(Unknown Source)
at com.ddtek.jdbc.oabase.BaseStatement.executeQueryInternal(Unknown Source)
at com.ddtek.jdbc.oabase.BasePreparedStatement.executeQuery(Unknown Source)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:310)
... 6 more
Caused by: java.sql.SQLException: [DataDirect][OpenAccess SDK JDBC Driver][OpenAccess SDK SQL Engine]AHD12002:Error in QueryHandler ServiceDesk-1401896396-1 in method fetch_response: AHD03053:Bad where clause: Parse error at : "active = 1 AND type = 'R' AND ( assignee.combo_name IS NOT NULL ) AND ( assignee.combo_name IN ('*') OR N'*' IN ('*') )" (syntax error)
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.execute(Unknown Source)
at com.ddtek.jdbc.openaccess.OpenAccessImplStatement.execute(Unknown Source)
at com.ddtek.jdbc.oabase.BaseStatement.commonExecute(Unknown Source)
at com.ddtek.jdbc.oabase.BaseStatement.executeQueryInternal(Unknown Source)
at com.ddtek.jdbc.oabase.BasePreparedStatement.executeQuery(Unknown Source)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:310)
... 6 more