On conditional query in Jaspersoft Studio.

[mbmorgan]

The query dialog box in Jaspersoft Studio will not let me "read fields" with my query.  Let me show what I have:

A Query_Input_Control parameter, Integer class, set for prompting, to accept 1, 2, 3, etc.

A Query_Controller parameter, String class, NOT set for prompting, with default value expression like:

$P{Query_Input_Control} == 1

? $P{Query_1}

: $P{Query_Input_Control} == 2

? $P{Query_2}

: "select  * from table where column = 'value'"

The Query_1 and Query_2 are themselves parameters, String class, and NOT set for prompting, with default value expressions as the queries I want to run based on the input control.

As for the actual query for the report, I have simply:

$P!{Query_Controller}

 

Upon clicking "Read Fields" in the dialog box, I get an error: "Expected lexical element not found: CREATE, DROP, SELECT, INSERT..." which I find odd, since my conditional queries are vanilla SELECT statements. 

 

What am I doing wrong?  Any assistance is appreciated.

Thanks, MBM.

 

 

 

 

 

 

[marianol]

I have not tested this is what i belive it's happening.
Does you "Query_Input_Control" have a default value? If you don't then JSS will not know which query to use by default when you press Read Fields. Try adding a default value and dee if it works for you.

 

[mbmorgan]

Thanks for your response!

 

Yes, the Query_Input_Control is of class java.lang.Integer and the default value is 1 (no quotes necessary for an integer, correct?). No success upon hitting Read Fields. However, if it helps you gain some insight into the matter, my sub-queries do contain parameters, and they look like:

 

"SELECT count(field1), field2 from table WHERE field3 =" + $P{Param_1} + " AND $X{IN, field4, Param_2} AND $X{IN, month(field5), Param_3} AND + year(field5) =" + $P{Param_6} + " group by field2"

 

I have the sub-queries set as string class, so the entire query is wrapped in quotes, except for skipping occurrences of Parameters, which I have concatenated. Am I doing something wrong here? Thanks again for your assistance.

[mbmorgan]

Hi again... you may now disregard this question as I have now figured out something that worked...it was a matter of correcting the syntax in my sub-queries and assigning the correct data types to the parameters.

Thanks again, M. B. Morgan