How to reference java.lang.Boolean parameter in my SQL query?

[cbarlow3]

I've created iReport parameters of type java.lang.Boolean before and found that I can type in   true   or    false    while testing in iReport and use the checkbox input control after the report has been deployed to JasperReports Server.  And I can use the parameter in Groovy scripts for Print When expressions as though it naturally evaluates to a logical true or false, etc:     $P{booleanParameter}  &&  !$F{someTextField}.isEmpty()    for example.

BUT...how can I use a Boolean parameter in my SQL parameter?  For example, let's say I want a checkbox that reads "Include Closed Accounts?"  I define $P{includeClosed} as type java.lang.Boolean, and I set the default value to     false     (no quotes around it, all lowercase).   So far, so good.  But what is my SQL query?

SELECT  ACCOUNT.NUMBER FROM CORE.ACCOUNT AS ACCOUNT

WHERE ACCOUNT.CLOSE_DATE IS NULL OR $P{includeClosed}

is what I'd like to do, but the SQL parser in iReport seems to balk at me using the Boolean parameter as an actual true or false value.

I also tried   WHERE ACCOUNT.CLOSE_DATE IS NULL OR $P{includeClosed}='true'  , which gets past the syntax checker, but it never evaluates to true (I only get open accounts, regardless of what I enter for this parameter).  I also tried casting the parameter to Boolean, but I don't think there is such a thing in SQL, nor do I understand why it should be necessary in this case.

I've temporarily changed this and two similar Boolean parameters to instead be string, where I assume the value is either "T" or "F", and in JasperReports Server, I have in fact set up a single select from list of values input control where I translate "Yes" and "No" choices from a dropdown to "T" and "F", but selecting Yes and No from a dropdown isn't nearly as convenient for the user as checking or not checking a single checkbox.

Any ideas?  Thanks.

Carl

[mdahlman]

I guess that you can't just put the Boolean into most SQL dialects. I suspect PostgreSQL would handle it.

I think you'll find the 2-parameter solution best for this case. The user has a checkbox for $P{includeClosed} as type java.lang.Boolean. Then you have a String parameter $P{includeClosedSQL}. Don't prompt for this one. Give it a default value like this:

$P{includeClosed} ? "OR ACCOUNT.CLOSE_DATE IS NOT NULL" : ""

Then your SQL can be this:

SELECT  ACCOUNT.NUMBER FROM CORE.ACCOUNT AS ACCOUNT
WHERE ACCOUNT.CLOSE_DATE IS NULL $P!{includeClosedSQL}

Regards,
Matt

 

[bgilliam]

With the iReport Query Language as SQL, using 1 or 0 instead of 'true' or 'false' in query expression worked for me.  Example:

WHERE
     COALESCE(Orders."date_OrderDropDead",Orders."date_OrderRequestedToShip") BETWEEN (CASE WHEN($P{true_false_input}= 1) THEN CAST($P{date_start}-120 AS DATE) ELSE $P{date_start} END) AND $P{date_end}

Above condition shows a date range going back an extra 120 days if  true_false_input is set to 'true'.  SQL seems to read that as 1 and 'false' as 0.