How to Handle null values when passing nothing in Double Select Query parameter

[testinginvestingapp]

I am generating this report based on the following: 

User is required to input minesite but it is optional to input typeOfcertificate. 

Below is a screenshot of JRXML code where I am using both of the highlighted parameters to generate data. 

Is it possible to use either script like this in the JRXML jasperReport file:

IF (typeOfcertificate is Null) 

<queryString language="SQL">
 <![CDATA[sELECT FC.LAST,FC.FIRST,FL.LEVEL_DESC, 
AE.EMP_DESC AS AreaOfEmployment

FROM [dbo].[FS_AREA_OF_EMPLOYMENT] AE
INNER JOIN [dbo].[MINES_CERTIFICATIONS] MC ON (AE.EMP_CODE=MC.AOE_EMP_CODE)
INNER JOIN [dbo].[FS_CLIENTS] FC ON (MC.CLI_PEOPLE_ID=FC.PEOPLE_ID)
INNER JOIN [dbo].[FS_LEVELS] FL ON (MC.SEM_LEVEL=FL.LEVEL_CODE)
LEFT join [dbo].[MINES_STATUS_LOG] MSL ON (MSL.ACTION_CERT_ID=MC.CERT_ID)
LEFT join [dbo].[FS_STATUSES] FS ON (MSL.Action_Type=FS.Status_Code)

WHERE (MC.AOE_EMP_CODE= $P{minesite} AND MC.EXPIRY_DATE >= GETDATE())
ORDER BY FC.LAST, MC.SEM_LEVEL]]>
 </queryString>

 

ELSE

The below script. The changes made to SQL code is highlighted. 

 

---------------------------------------------------------------------------------------- JRXML CODE ------------------------------------------------------------------------------------------------------------------------------------
<parameter name="minesite" class="java.lang.String"/>
<parameter name="typeOfcertificate" class="java.lang.String"/>

<queryString language="SQL">
 <![CDATA[sELECT FC.LAST,FC.FIRST,FL.LEVEL_DESC, 
AE.EMP_DESC AS AreaOfEmployment

FROM [dbo].[FS_AREA_OF_EMPLOYMENT] AE
INNER JOIN [dbo].[MINES_CERTIFICATIONS] MC ON (AE.EMP_CODE=MC.AOE_EMP_CODE)
INNER JOIN [dbo].[FS_CLIENTS] FC ON (MC.CLI_PEOPLE_ID=FC.PEOPLE_ID)
INNER JOIN [dbo].[FS_LEVELS] FL ON (MC.SEM_LEVEL=FL.LEVEL_CODE)
LEFT join [dbo].[MINES_STATUS_LOG] MSL ON (MSL.ACTION_CERT_ID=MC.CERT_ID)
LEFT join [dbo].[FS_STATUSES] FS ON (MSL.Action_Type=FS.Status_Code)

WHERE (MC.AOE_EMP_CODE= $P{minesite} AND (MC.SEM_LEVEL=$P{typeOfcertificate} or $P{typeOfcertificate} is null) AND MC.EXPIRY_DATE >= GETDATE())
ORDER BY FC.LAST, MC.SEM_LEVEL]]>
 </queryString>

[jgust]

You can use coalesce() to handle the null. 

The query below will work when $P{typeOfcertificate} is null or has a value.

SELECT FC.LAST     , FC.FIRST     , FL.LEVEL_DESC     , AE.EMP_DESC AS AreaOfEmploymentFROM [dbo].[FS_AREA_OF_EMPLOYMENT] AE    INNER JOIN [dbo].[MINES_CERTIFICATIONS] MC ON (AE.EMP_CODE=MC.AOE_EMP_CODE)    INNER JOIN [dbo].[FS_CLIENTS] FC ON (MC.CLI_PEOPLE_ID=FC.PEOPLE_ID)    INNER JOIN [dbo].[FS_LEVELS] FL ON (MC.SEM_LEVEL=FL.LEVEL_CODE)    LEFT JOIN [dbo].[MINES_STATUS_LOG] MSL ON (MSL.ACTION_CERT_ID=MC.CERT_ID)    LEFT JOIN [dbo].[FS_STATUSES] FS ON (MSL.Action_Type=FS.Status_Code)WHERE MC.AOE_EMP_CODE = $P{minesite}   AND MC.SEM_LEVEL = coalesce($P{typeOfcertificate},MC.SEM_LEVEL)  AND MC.EXPIRY_DATE >= GETDATE()ORDER BY FC.LAST, MC.SEM_LEVEL[/code]

 

[yama818]

I am using MySQL and was able to achieve this with the second method.

I have changed your SQL idea a bit.
The actual test SQL is below.
(you may need to rewrite it for MSSQL)

SELECT * FROM test.t26WHERE minesite = $P{minesite}ANDCASEWHEN $P{typeOfcertificate} is nullTHEN   1=1ELSE  typeOfcertificate = $P{typeOfcertificate}END

* Case1 (typeOfcertificate is not null)

* Case2 (typeOfcertificate is null)