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

I want to generate a report using iReport. I should give Area_code, units, condi开发者_JS百科tion (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?

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.

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};

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}