Jump to content

query syntax with parameters for jaspersoft studio


Recommended Posts

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:
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
$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{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?
Link to comment
Share on other sites

  • Replies 2
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...