Need to construct dynamic SQL query where null-value string parameter will evaluate to "WHERE 0=0"

0

I am using Jasper Studio 6.8 to construct a report which will fetch data from an Oracle database. I need to build a data selection query dynamically, based on user-selected input parameter values. The selection parameters include both single-value and multi-value pramaters. For ease of reference, I will call the single-value parameter "some_id" text string, and the molti-value parameter "states_list", consisting of a list of state_name text strings. User selecttion of either of these parameters is optional.

If the user selects both parameters, the dynamicaaly constructed query should be as follows:
SELECT SOME_FIELD FROM SOME_TABLE WHERE ID = 'some_id' AND STATE IN ('state1_name', 'state2_name', etc.);

If the user selects only the some_id parameter, the dynamicaaly constructed query should be as follows:
SELECT SOME_FIELD FROM SOME_TABLE WHERE ID = 'some_id' AND 0=0;

If the user selects only items to populate the states_list parameter, the dynamicaaly constructed query should be as follows:
SELECT SOME_FIELD FROM SOME_TABLE WHERE 0=0 AND STATE IN ('state1_name', 'state2_name'); 

I can achieve the desired behaviour for "states_list" by using the construct $X(IN, STATE, states_list). The $X(IN..." function evalutes to 0=0 when sttes_list is null, which is what I want.

Is there any way to achieve the desired behaviour for "some_id" single-value string parameter using Jasper built-in finctions? 
.
According to Jasper documentation "Using Parameters in Queries", if the some_id parameter is null, $P(some_id) evaluetes to some_id = NULL, which is not what I want (and is also syntactically incorrect). 
If I try to use "$X(EQUAL..." construct for this purpose, the problem I encounter is different null-handling behaviour for "$X(IN...)" vs. $X(EQUAL...)". 
If some_id is null, $X(EQUAL, ID, some_id) will not evaluate to "0=0" as it does for "$X(IN...)", instead it evaluate to "some_id IS NULL", which is also not what I want. 

I realize that I can achieve my desired result by defining "some_id" as a "ids_list" list which will always only have only one value, and then using "$X(IN, ID, ids_list). But this strikes me as a rather clunky workaround. It seems to me that what I am asking for would be a very common requirement, which makes me wonder whether I missed something in the Jasper documentation.

Is there any way to achive my desired result using Jasper built-in functionality? 
If the answer is no, then I would appreciate advice regarding the best way to achieve my desired result.

dario.romani's picture
Joined: Apr 11 2019 - 10:51am
Last seen: 5 days 3 hours ago

1 Answer:

0

I'm not sure if the "newer" versions of JasperReports have something built-in, but I've done something similiar some years ago for our interactive reporting within our ERP-system with JR 5.5.1, but can't access the jrxmls anymore (as I changed the company)

But I'll try to explain.... Just add an "invisible" Parameter (that isn't for prompting) and use only this for your query instead of the origin parameters.

Something like:

  • $P{state_id} (= is interactive/for prompting)
  • $P{state_list} (= is interactive/for prompting)
  • $P{hidden4query} (=is NOT interactive/prompting)

now create as DefaultParameterExpression for "hidden4Query" an IfThenElse syntax:    $P{state_id} != null? "AND ID = " + $P{state_id} : AND STATE IN " + $P{state_list}

in your query you could now use: "Select ... From .... Where 0= 0 " + $P!{hidden4query}

I'm not sure where exactly the exlamation sign "!" was placed... just play around a bit  :-) The In Operator was a bit Tricky but I'm sure, that I used that also in the calculated parameter expression... either with the simple IN sytnax or the more compley $X(IN..) syntax... but this is for you.

Should work... and is still working at my old customers :-)

hth + regards

C-Box

C-Box's picture
1245
Joined: Jul 19 2006 - 5:58pm
Last seen: 12 hours 29 min ago
Feedback