need help on constructing an SQL query.
my requirement is something like this.
generate a report on task ( with statuses RUNNING, COMPLETED, FAILED). The status is passed on from the screen (A combo box) and jasper will generate the report. The query i need to attach to the .jasper report is something like this.
SELECT X,Y,Z
FROM task_table
WHERE status IN (CASE WHEN $P{STATUS}='ALL' THEN (SELECT different_tasks_statuses FROM FEPS.TASK_STATUS)ELSE $P{STATUS} END)
where $P{STATUS} is the report parameter whose value is passed from the screen based on the combo box (drop down text) selection.
i can create report if we query it for a specific status. but there is a special case, where when we select 'ALL' from the screen, report should fetch tasks belonging to all statuses (see the query condition in the above query).
1. i can do this by designing a separate report (separate query then) for this special case (passing ALL). but i dont want to do that since there are amny filter combos with 'ALL'.
2. neither can i pass a dynamic where clause to the query generator
if somehow we can construct a string literal i think we can do this with a decode or case statement.
if you have any clue, pls let me know fast.
thanks,
Rajeev
7 Answers:
| Code: |
<br />
<parameter name="STATUS"/><br />
<parameter name="whereClause" isForPrompting="false"><br />
<defaultValueExpression><br />
$P{STATUS}.equals("ALL"«») ? "" : (" WHERE status = '" + $P{STATUS} + "'"«»)<br />
</defaultValueExpression><br />
</parameter><br />
<queryString><br />
SELECT .. FROM .. $P!{whereClause}<br />
</queryString><br />
</td></tr></tbody></table><br />
<br />
This solution has the disadvantage that the status is no longer sent as prepared statement parameter. If you think this is a problem, you can easily find a trick around this (e.g. create parameter having <i>$P{STATUS}.equals("ALL") ? "true" : "false"</i> as default value and use <i>WHERE ($P!{yourParam} OR status = $P{STATUS})</i> in the query).<br />
<br />
HTH,<br />
Lucian |
it worked fine with the whereClause parameter as u suggested.
but i didnt understand your tip on passing preparedstatment parameter. i tried changing the query to
SELECT X,Y,Z
FROM task
WHERE $P!{IS_TRUE} OR STATUS = $P{STATUS}
which will thro ORA-00920:ÂinvalidÂrelationalÂoperator exception.
where IS_TRUE is defined as $P{STATUS}.equals("ALL") ? "true" : "false".
i also have one more doubt, whats the difference between specifying parameter as $P{STATUS} and $P!{STATUS}.
thanks,
Rajeev
Regarding the query error, obviously true and false are not Oracle literals, so you'll have to use something like $P{STATUS}.equals("ALL") ? "0 = 0" : "0 = 1".
HTH,
Lucian
Post edited by: lucianc, at: 2006/11/21 15:26
Hi Rajeev!
I have the same problem as yours, i need to do something like this.
Generate a report on Vehicle ( with statuses RUNNING, Stopped). The status is passed on from the screen (A combo box) and jasper will generate
the report. The query i need to attach to the .jasper report is something like this.
SELECT X,Y,Z
FROM vehicle
where vehicleId=1234
and status ?? (should can be either Running , stopped or ALL as selected by the user from the combo box on the screen.
If the user selects ALL in that case the records for both the status should be displayed)
Regards
Puneet
We are looking answers for following Questions
1. How to embed custom DynamicSchemaProcessor in Jasper Report Server (We would like to use that to dynamically modify the schema based on user credentials in the query.
2. Is there any way to edit the query ( replacing Query Parameter in the sql query) programmatically before it is executing.
basically we need to change the 'where' clause based on user credentails.
Many Thanks
