CA Service Management

 View Only
  • 1.  parameter issue for assignee_combo_name in Jaspersoft report

    Posted May 23, 2017 09:50 AM

    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



  • 2.  Re: parameter issue for assignee_combo_name in Jaspersoft report
    Best Answer

    Broadcom Employee
    Posted May 23, 2017 09:58 AM

    Hi Vchini,

     

    This is because you are using combo_name in the where clause to filter the query. This attribute is "Derived" and cannot be used as a filter.

     

    Please check the following document as a reference:

     

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

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

     

    Regards,

    Pablo



  • 3.  Re: parameter issue for assignee_combo_name in Jaspersoft report

    Posted May 23, 2017 10:09 AM

    Hi Pablo,

    Thanks for providing these references.

    How about assignee_department_name parameter? This field was created using an alias attribute in SDM.

     

    Thank You!



  • 4.  Re: parameter issue for assignee_combo_name in Jaspersoft report

    Broadcom Employee
    Posted May 23, 2017 10:17 AM

    Hi Vchinni,

     

    If it's not Derived, then it should work. Can you try removing combo_name from the where clause and then re-run the query.? What is the result and error message?

     

    Thanks!

    Regards,

    Pablo



  • 5.  Re: parameter issue for assignee_combo_name in Jaspersoft report

    Posted May 23, 2017 12:32 PM

    It was a typo and was able to fix it. Thanks Pablo for your help.