query syntax with parameters for jaspersoft studio

[gbaldeck87]

I have a query in a report I'm creating which is throwing the following error:

Caused by: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)

at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)

at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)

 

I've been messing around with my query syntax but I can't seem to get anything to work. Here's my query:

http://pastebin.com/26vm5V1F

 

I made a pastebin because the code block option keeps messing with my formatting. The only difference there is imagine every line is surrounded by quotes except for the lines that have a paremeter. Like the lines that have +$P{V_LIMIT_AL}+ and +$P{V_LIMIT_BY_AL_TYPE}+. I removed the quotes around the query lines so that the SQL formatting would make it easier to read.

 

The actual query is in a parameter called $P{REPORT_QUERY} which is passed to the report in the DataSet and Query editor dialog as $P!{REPORT_QUERY} (notice the exclamation mark) . Which is why there are quotes around the lines in the parameter's expression editor.

 

My guess is that somehow the $P{V_LIMIT_AL} and $P{V_LIMIT_BY_AL_TYPE} parameters are not being evaluated by the query properly. Here's what I have for eaches default value expression.

 

Query Link

-----------------

http://pastebin.com/26vm5V1F

 

$P{V_LIMIT_AL}

----------------------------

$P{airlineId} != null && !$P{airlineId}.equals("") ? " AND A.AIRLINE_ID = /'$P!{airlineId}/' "  : ($P{airlineName} != null && !$P{airlineName}.equals("") ? " AND C.COMPANY_NAME = /'$P!{airlineName}/' " : "")[/code]

 

$P{V_LIMIT_BY_AL_TYPE}

----------------------------------------

$P{airlineType} != null && ($P{airlineType}.equals("Cargo") || $P{airlineType}.equals("Passenger")) ? " AND S.STAT_DC1 = /'$P!{airlineType}/' " : ""[/code]

 

Can you see anything wrong with how I have my query setup and how the parameters are being passed to it?

 

[rsuddhala]

Hi ,

Have you run the query in query browser with some parameters ?  If not .Run the query with some values in query browser.

If you execute successfully in query browser then the problem with design of report/passing paramters.

 

 

Thanks,

Raviraj

[gbaldeck87]

Hi Raviraj, yes I ran the query with parameters in SQL Developer. It works fine. I'm thinking it has to do with how I'm using the parameters but I've tried so many different things now that I don't know what to do next.

Looking at how I'm passing the parameters do you see where I could be going wrong? Particularly with the way I'm using the single quotes around the parameters and the exclamation marks.