CODEWITHSUNDEEP
sqljasper-reports
Ahmed Wahbi
Ahmed Wahbi

Reputation: 128

How can I disable the where clause at the query when the parameter is not passed to the the report?

I am designing a JasperReports report with iReport and my query is like this:

select * from TABLE_NAME where COLUMN_NAME = $P{PARAMETER_NAME}

my problem starts when I try to make it generic i.e. when the parameter is not passed I want the query to bring all the data from the table without filtering. I tried to put a default value for the parameter as "all(PARAMETER_NAME)" but that would only work when referring to the parameter with $P!{PARAMETER_NAME} which means the parameter value will be treated as part of the query thus the query becomes

select * from TABLE_NAME where COLUMN_NAME = $P!{PARAMETER_NAME}

but in this case the parameter must either not be passed or passed as an SQL query which is not wise option when sending the parameter from Java application for many considerations. waiting for your suggestions.

Upvotes: 0

Views: 1012

Answers (3)

Endrik
Endrik

Reputation: 2258

I think using the UNION operator would be enough. For example:

select * from TABLE_NAME where COLUMN_NAME = $P!{PARAMETER_NAME} and $P!{PARAMETER_NAME} is not null
union
select * from TABLE_NAME

It means,in case the criteria for the first block is not meant (in this case when the parameter in not entered, so it will have a null value), then it will execute the second block, the one without where clause. Remember, some time could be required to use UNION ALL, instead of union.

Upvotes: 1

Rohan
Rohan

Reputation: 2030

try this : it is specific to oracle, but works for the situation mentioned by you SELECT * FROM TABLE_NAME TM WHERE (CASE WHEN $P!{PARAMETER_NAME} IS NULL THEN 'TRUE' ELSE (DECODE(TM.COLUMN_NAME, '1001', 'TRUE', 'FALSE')) END) = 'TRUE'; Firstly, if the check of '$P!{PARAMETER_NAME} IS NULL' fails and it moves to ELSE part and depending upon match, it returns values.

SELECT * FROM TABLE_NAME TM WHERE (CASE WHEN $P!{PARAMETER_NAME} IS NULL THEN 'TRUE' ELSE (DECODE(TM.COLUMN_NAME, '1001', 'TRUE', 'FALSE')) END) = 'TRUE'; Here the case statement decides if the parameter is to be checked. In case it equates to NULL, it will return TRUE, which will match with the TRUE after equal sign and return all records.

You may use case statement instead of DECODE SELECT * FROM TABLE_NAME TM WHERE (CASE WHEN '1001' IS NULL THEN 'TRUE' ELSE (CASE WHEN TM.COLUMN_NAME = '1001' THEN 'TRUE' ELSE 'FALSE' END) END) = 'TRUE';

Upvotes: 1

Ahmed Wahbi
Ahmed Wahbi

Reputation: 128

I found that I can do it by the following:

select * from TABLE_NAME where COLUMN_NAME = $P{PARAMETER_NAME} or $P{PARAMETER_NAME} is NULL

if there is better suggestions please provide.

Upvotes: 3

Related Questions