syntax error, unexpected NAME, expecting '('

0

I am trying to create a report which prompts the user with a list of names and user can select either * or a single name or multiple names, these names are then passes to the query to run the report.  In this following example I had selected the following three values:  Alanton Elementary School, Applicants, Arrowhead Elementary School.  I am running into a syntax error and do not understand how to correct this issue, please help:

Error Message:

The server has encountered an error. Please excuse the inconvenience.

Error Message

Error filling report

Error Message

net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query: SELECT category_symbol, ref_num, open_date, assignee_organization_name FROM cr WHERE (assignee_organization_name in [Alanton Elementary School, Applicants, Arrowhead Elementary School])

Error Message

java.sql.SQLException: [DataDirect][OpenAccess SDK JDBC Driver][OpenAccess SDK SQL Engine]syntax error, unexpected NAME, expecting '(' at assignee_organization_name Alanton

My SQL is as following:

SELECT 
    category_symbol,
    ref_num,
    open_date,
    assignee_organization_name
    
FROM cr
WHERE
(assignee_organization_name in $P!{R_Select_Organization})

 

ggiran's picture
2
Joined: Apr 17 2018 - 9:13am
Last seen: 1 year 10 months ago

I had also tried the following where clause: WHERE
$X{IN, assignee_organization_name, R_Select_Organization}  and had received this error message:

Error Message

net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query: SELECT category_symbol, ref_num, open_date, assignee_organization_name FROM cr WHERE assignee_organization_name IN (?, ?, ?)

Error Message

java.sql.SQLException: [DataDirect][OpenAccess SDK JDBC Driver][OpenAccess SDK SQL Engine]syntax error, unexpected '?' at ( ?

ggiran - 2 years 1 month ago

1 Answer:

0

If you put $P!{R_Select_Organization} , with ! symbol, replace literally with what you put in R_Select_Organization parameter.

So if you only want replace with list values you must put $P{R_Select_Organization}

Mariano

mlopez_1's picture
935
Joined: Oct 21 2013 - 8:08am
Last seen: 1 day 1 hour ago
Feedback
randomness