CODEWITHSUNDEEP
postgresqlselectif-statementjasper-reportsireport
oneofakind
oneofakind

Reputation: 562

SQL Statement If statement inside a SELECT statement

Is there a way that I can use if statement in a select statement?

I cannot use Case statement in this one. Actually I am using iReport and I have a parameter. What I want to do is if a user does not enter a certain parameter it will select all the instances.

For example a table of person referencing to gender table, a with a parameter on where clause, query would go like:

SELECT * FROM persontable WHERE gender_id = $P{genderName}:numeric

If the parameter $P{genderName} is null or blank then it will select all. I tried this query:

SELECT * FROM persontable WHERE gender_id IN (SELECT CASE WHEN $P{genderName} 
 IS NULL THEB (SELECT id from genderTable 
 WHERE id = $P{genderName}:numeric) ELSE $P{genderName}::numeric END)

But it will return an error "More that one row is returned....." I think it is because of the CASE statement. How do I this?

Upvotes: 0

Views: 926

Answers (2)

Gopinagh.R
Gopinagh.R

Reputation: 4916

This can be easily achieved using an Auxiliary Parameter.

Define a new parameter in your report, call it, aux_gendername.

While defining the parameter, tweak its Default Value Expression. 

($P{gendername}.equals("") || $P{gendername}==NULL? " " : " and gender_id = '"+$P{gendername}+"'")

Then in your main query, instead of the actual parameter ($P{gendername}) use the auxiliary parameter. 

SELECT * FROM persontable WHERE 1=1 $P!{aux_gendername}

If the $P{gendername} contains any value and condition will dynamically be appended to the query else the entire resultset would be displayed in the report without any filtering.

Note: Using a ! in the auxiliary parameter is mandatory.

Upvotes: 1

marvin
marvin

Reputation: 479

Try this: 

SELECT * FROM persontable 
WHERE gender_id = $P{genderName} OR $P{genderName} IS NULL

Upvotes: 3

Related Questions