Getting "You have an error in your SQL syntax" for report with parameterized query

Question

I want to generate a report using iReport. I should give Area_code, units, condition (meaning < > =) as parameters. I want to get the some fields from customer table which satisfy the condition units < or > or = number of units in calculatedbillamounthistory table.

As a example,

SELECT Cus.Cusname,Cus.Cus_Address,Cus.CusID 
FROM customer Cus, calculatedbillamounthistory Cal 
WHERE Cal.consumedunits > 90 And Cus.CusID = Cal.cusid AND Cus.Area_code = Hr0111.

I should pass '>', '90', 'Hr0111' values as parameters.

This is my query:

SELECT Cus.Cusname,Cus.Cus_Address,Cus.CusID FROM customer Cus, calculatedbillamounthistory Cal WHERE Cal.consumedunits $(condition) $(units) AND Cus.CusID = Cal.cusid AND Cus.Area_code = $(Area_code);

When I give the query to jaspersoft it will show the error:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$(condition) $(units) And Cus.CusID = Cal.cusid AND Cus.Area_code = $(Area_code)' at line 1"

Can someone help me please?

Possible duplicate of [JasperReports: Passing parameters to query](http://stackoverflow.com/questions/11871042/jasperreports-passing-parameters-to-query) — Alex K, Jul 12 '16 at 19:29

score 0 · Answer 1 · answered Dec 12 '12 at 11:54

Use Single Value input control for condition and unit parameter and try this query in Ireport

  SELECT Cus.Cusname,Cus.Cus_Address,Cus.CusID 
  FROM customer Cus,calculatedbillamounthistory Cal 
  WHERE Cal.consumedunits $P{condition} $P{units} 
        AND Cus.CusID = Cal.cusid 
        AND Cus.Area_code = $P{Area_code};

score 0 · Answer 2 · answered Dec 12 '12 at 15:13

You can outbound whole WERE part into parameter and append this to your query:

Parameter: WHERE (String)

WHERE=" Cal.consumedunits > 90 And Cus.CusID = Cal.cusid AND Cus.Area_code = Hr0111"

SQL: ELECT Cus.Cusname,Cus.Cus_Address,Cus.CusID FROM customer Cus, calculatedbillamounthistory Cal WHERE $P!{WHERE}

score 0 · Answer 3 · answered Jun 30 '11 at 08:13

What about using the $P!{..} syntax for parameters, such as:

SELECT Cus.Cusname,Cus.Cus_Address,Cus.CusID FROM customer Cus, calculatedbillamounthistory Cal WHERE Cal.consumedunits $P!{condition} $P!{units} AND Cus.CusID = Cal.cusid AND Cus.Area_code = $P!{Area_code};

The $P!{..} syntax evaluates the parameters before executing your query. With $P{..} prepared statements would be used, which at least for the condition parameter doesn't work.

Getting "You have an error in your SQL syntax" for report with parameterized query

3 Answers3