Running SQL script based on user input

[testinginvestingapp]

I am accepting two parameter from the user through a website.

• $P{first_Parameter}
• $P{second_Parameter}

<parameter name="first_Parameter" class="java.lang.String"/>
<parameter name="second_Parameter" class="java.lang.String"/>

I would like the following:

• If the $P{first_Parameter) is NULL then I would like to run SCRIPT 1
• Else I would like to run SCRIPT 2

In the JRXML file:

<queryString language="SQL">
<![CDATA[

IF ($P{first_Parameter)) == NULL then 

SELECT lastName, firstName, Address
WHERE schoolName = $P(second_Parameter)
FROM StudentTable;

ELSE 

SELECT lastName, firstName, Address
WHERE schoolName = $P(second_Parameter) and country = $P{first_Parameter)
FROM StudentTable;

]]>
</queryString>

 

It would be really helpful if you guys can support me with an answer in context of JRXML file. As I didn't use the JASPERSTUDIO UI to develop the report. 

• USED SOLUTIONS: 1 (Not Working)
  <queryString language="SQL
      <![CDATA[
  
      SELECT lastName, firstName, Address
      CASE WHEN ($P{first_Parameter}=null) then (WHERE schoolName = $P{second_Parameter})
      ELSE (WHERE schoolName = $P{second_Parameter} and country = $P{first_Parameter}") end
  
  ]]> </queryString>
  
   

• USED SOLUTIONS: 2 (Not Working)
  <queryString language="SQL
      <![CDATA[
  
     $P{first_Parameter}.equals(null)
  ? " SELECT lastName, firstName, Address
  WHERE schoolName = $P(second_Parameter)
  FROM StudentTable;"
  : " SELECT lastName, firstName, Address
  WHERE schoolName = $P(second_Parameter) and country = $P{first_Parameter)
  FROM StudentTable;"
  
  ]]> </queryString>

[jgust]

With the use of COALESCE() the two queries can be combined into one.

SELECT lastName, firstName, AddressFROM StudentTableWHERE schoolName = $P{second_Parameter}  and country = coalesce($P{first_Parameter},country)[/code]

 

 

My TEST DATASET

SELECT schoolname, lastName, firstName, Address, Countryfrom (    select 'El Dorado HS' as schoolname         , 'Smith'        as lastname         , 'Jane'         as firstname         , '123 Main St'  as Address         , 'Canada'       as country     from dual    union all    select 'El Dorado HS'         , 'Smith'         , 'Bob'         , '321 Main St'         , 'Spain'     from dual)where schoolname =  $P{second_Parameter}   and country =  coalesce($P{first_Parameter},country)[/code]

Test 1:

Results 1:

 

Test 2

Results 2:

[mlopez_1]

I think that the better approach is using jasper sentences for parameters like:

$X{[bETWEEN], schoolName , second_Parameter, second_Parameter}

AND

$X{[bETWEEN], country , first_Parameter, first_Parameter}

When a parameter value is null, replace its sentences with: 0=0

 

Regards