sql - optional where clause jasper reports

Question

I am trying this query but without success.

SELECT name, phone_office, billing_address_city, billing_address_street, billing_address_country 
FROM accounts
WHERE ($P!{EmployeeID} is null or assigned_user_id = $P!{EmployeeID})
ORDER BY billing_address_country, billing_address_city

This url will filter by EmployeeID and works fine:

.../flow.html?_flowId=viewReportFlow&reportUnit=/reports/samples/EmployeeAccounts&EmployeeID=sarah_id

But when i remove the EmployeeID parameter i want to remove the filter where. So all results should be shown.

.../flow.html?_flowId=viewReportFlow&reportUnit=/reports/samples/EmployeeAccounts

My question is, what is the correct way of passing an optional where in sql query.

Answer 1

Ok, let see the sample.

For example we have a query:

SELECT id, city, street FROM address WHERE city=$P{inputParamCity} ORDER BY city

But our inputParamCity can be undefined. In this case we got an error:

Error?filling?print...?Error?preparing?statement?for?executing?the?report?query?:?
SELECT?id,?city,?street?FROM?address?WHERE?city=??ORDER?BY?city

How we can fix it?
It is very simple - we can add another parameter with default expression like this:

<parameter name="whereClause" class="java.lang.String" isForPrompting="false">
    <defaultValueExpression><![CDATA[()$P{inputParamCity} == null || $P{inputParamCity}.isEmpty()) ? "1=1" : "city='" + $P{inputParamCity} + "'"]]></defaultValueExpression>
</parameter>

-if the inputParamCity parameter is undefined the "fake" clause "1=1" will be used, in other case the filter by city field will be applied.

And of course we have to modify the query expression - to use this new parameter. Our query expression in this case will be:

<queryString>
    <![CDATA[SELECT id, city, street FROM address WHERE $P!{whereClause} ORDER BY city]]>
</queryString>

The sample

The jrxml file:

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="optional_where_clause" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="d3648644-0087-4dfc-ac6d-87e82d9bb33e">
    <parameter name="inputParamCity" class="java.lang.String"/>
    <parameter name="whereClause" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA[($P{inputParamCity} == null || $P{inputParamCity}.isEmpty()) ? "1=1" : "city='" + $P{inputParamCity} + "'"]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[SELECT id, city, street FROM address WHERE $P!{whereClause} ORDER BY city]]>
    </queryString>
    <field name="ID" class="java.lang.Integer"/>
    <field name="CITY" class="java.lang.String"/>
    <field name="STREET" class="java.lang.String"/>
    <detail>
        <band height="20" splitType="Stretch">
            <textField>
                <reportElement uuid="c2a80b99-e087-4839-8e77-841edd899255" x="0" y="0" width="100" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{ID}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement uuid="0aafcfd6-60f7-4272-8e7d-0aa77507204b" x="100" y="0" width="100" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{CITY}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement uuid="c8726513-8250-43ec-bafc-003e81094c27" x="200" y="0" width="100" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{STREET}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>

In case using undefined inputParamCity parameter (the value is not set) the result will be:

In this case the query was used by engine is:

SELECT?id,?city,?street?FROM?address?WHERE?1=1?ORDER?BY?city

If we set, for example, the value Chicago for inputParamCity parameter the result will be:

In this case the query was used by engine is:

SELECT?id,?city,?street?FROM?address?WHERE?city='Chicago'?ORDER?BY?city

---

Notes:

• You can find more information in this post: JasperReports: Passing parameters to query

• You can modify the whereClause parameter's expression and the query expression. For example, you can move WHERE keyword from query expression to parameter's expression to prevent using the fake clause "1=1"