Select Statement with Parameters in Value Expression

[robables]

Hello,

Jaspersoft Studio Community 6.3.1.final.

I have a report setup that needs to do a conditional select statement.  I followed some instructions i found posted by another Jasper user, and created a parameter that contains the following syntax:

$P{status} == "closed" ? $P{status_closed_query} : $P{status_open_query}[/code]

I then populated the parameter $P{status_closed_query} with a select statement, and the $P{status_open_query} with a different select statement.

The issue I am having is the select statements inside the value expressions of each parameter contain other variables.  Example:

    select q.id as q_id,           w.document_id,           dt.id as dt_id,           dep.d_name,           q.q_name,           dt.dt_name,           CONVERT(varchar(10), w.wf_datein,   101) as wf_datein,           CONVERT(varchar(10), w.wf_dateout, 101) as wf_dateout      from workflow winner join queue      q   on w.queue_id       = q.id inner join document   d   on w.document_id    = d.id inner join doctype    dt  on d.doc_type_id    = dt.idinner join department dep on dt.department_id = dep.id      where wf_datein between $P{startDate} and $P{endDate}        and $X{IN, dt.id, doctypeId}        and $X{IN, q.id,  queueId  }        and dt.department_id = $P{departmentId}        and wf_dateout is null[/code]

This select statement works fine when it is in the main query dialog screen, but when it is in the parameter value expression, I receive the following error when trying to "Read Fields":

"Invalid column name 'param_startDate'"

startDate being the first param it encounters.  If I put a regular select in there like "select * from workflow", it works fine.

Do I need to use a different syntax for those parameters when they are inside the value expression of another parameter? 

Thank you in advance!

Rob

[robables]

Anyone?

[Siddharth Kothari]

Yes, in your SELECT statement instead of $P{param_name} you need to use $P!{param_name}.

[robables]

Thank you for your assistance.

However, now I am receiving the error "invalid syntax near  the keyword 'and'".

 

Do I need to do something special with the $X{IN, dt.id, doctypeId} statements as well?

[Siddharth Kothari]

There is no need of modifying $X{} expression. It seems to be a syntax error in your expression. Check for opening and closing parenthesis.

[robables]

The queries work fine if I paste them into the Dataset and Query dialog as the primary query on the report.  The read fields works perfectly for each for them when I paste them in individually.  So, I know the syntax of the queries are correct.

It only fails when I have them in the parameters as queries.

Now that I added the ! to the statement, the original error is gone, but the syntax error is now there. 

Here is the current query with your changes:

 

select q.id as q_id, w.document_id, dt.id as dt_id, dep.d_name, q.q_name, dt.dt_name, CONVERT(varchar(10), w.wf_datein, 101) as wf_datein, CONVERT(varchar(10), w.wf_dateout, 101) as wf_dateout from workflow w inner join queue q on w.queue_id = q.id inner join document d on w.document_id = d.id inner join doctype dt on d.doc_type_id = dt.id inner join department dep on dt.department_id = dep.id where wf_dateout between $P!{startDate} and $P!{endDate} and $X{IN, dt.id, doctypeId} and $X{IN,q.id, queueId} and dt.department_id = $P!{departmentId}

 

[robables]

The issue is still the parameters in the where clause.  If I remove them, and leave in the other portions of the where clause like "$X{IN, dt.id, doctypeId} and $X{IN,q.id, queueId}", it works fine.  As soon as I ad in a single parameter item like " and dt.department_id = $P!{departmentId}", it fails with a syntax error.

[Siddharth Kothari]

Try to use below in your expression:

"select q.id as q_id, w.document_id, dt.id as dt_id, dep.d_name, q.q_name, dt.dt_name, CONVERT(varchar(10), w.wf_datein, 101) as wf_datein, CONVERT(varchar(10), w.wf_dateout, 101) as wf_dateout from workflow w inner join queue q on w.queue_id = q.id inner join document d on w.document_id = d.id inner join doctype dt on d.doc_type_id = dt.id inner join department dep on dt.department_id = dep.id where wf_dateout between "+$P{startDate}+" and  "+$P{endDate}+" and $X{IN, dt.id, doctypeId} and $X{IN,q.id, queueId} and dt.department_id = "+$P{departmentId}