77443

Passing SQL operators to query through iReport Parameter

Question:

I am trying to create a report in iReport based on an oracle SQL query which deals with transactions. The actual tables involved are very lengthy and are not really relevant to the issue. I am trying to create a report that will return results when the transaction amounts meet 1 of the 3 following criteria:

<ul><li>between 1000.00 and 2499.99</li> <li>between 2500.00 and 9999.99</li> <li>>= 10000.00</li> </ul>

I am trying to create a single select list parameter where the person running the report can choose between one of the above specifications. Before I actually create the parameter I have been trying to test the query in SQL Developer. The line of the query where I am trying to implement this is:

alias.transaction_amount :amountRange

and my test input is "between 1000 and 9999". When that is hardcoded in the query, it works fine, but when I try and pass it in through this input I get an "Invalid Relational Operator" error.

Is there another way of doing this?

Answer1:

Try to use like this in query: <br /> select ... from table1 where transaction_amount $P!{paramReport}

From JasperReports Ultimate Guide:

$P{paramName} Syntax The parameters are used like normal java.sql.PreparedStatement parameters, using the following syntax: <queryString> <![CDATA[ SELECT * FROM Orders WHERE OrderID <= $P{MaxOrderID} ORDER BY ShipCountry ]]> </queryString> $P!{paramName} Syntax Sometimes it is useful to use parameters to dynamically modify portions of the SQL query or to pass the entire SQL query as a parameter to the report-filling routines. In such cases, the syntax differs a little, as shown in the following example. Notice the ! character: <queryString> <![CDATA[ SELECT * FROM $P!{MyTable} ORDER BY $P!{OrderByClause} ]]> </queryString>

Answer2:

After talking with some colleagues, we discovered a better solution to this problem, while I don't know if the method I was looking for is actually possible, this solution is much more intuitive.

alias.transaction_amount between (CASE WHEN $P{Range} = 0 THEN 1000.00 WHEN $P{Range} = 1 THEN 2500.00 ELSE 10000.00 END) and (CASE WHEN $P{Range} = 0 THEN 2499.99 WHEN $P{Range} = 1 THEN 9999.99 ELSE 9999999.99 END)

Using case statements with a switch parameter works perfectly, granted that if there's ever a transaction which is 1 billion dollars or greater they will be excluded but the maximum number can be adjusted as necessary.

Recommend

  • How to alias an executable using Powershell permanently?
  • Extend Name Box
  • Express displaying mongodb documents in Jade
  • Search files(key) in s3 bucket takes longer time
  • JavaScriptCore External Arrays
  • passing parameter to DownloadStringCompletedEventHandler in C#
  • How do I check if System::Collections:ArrayList is empty / nullptr / null?
  • Deduce parent class of inherited method in C++
  • Processing different annotations with the same Processor instance
  • Google analytics measurement protocol session timeout and query time limits
  • crash in __tcf_0
  • Get a trait object reference from a vector
  • LESS CSS how to modify parent property in mixin
  • Passing variable arguments using PowerShell's Start-Process cmdlet
  • Cannot get the UserManager class
  • Unable to decode certificate at client new X509Certificate2()
  • Insert new calendar with SyncAdapter- Calendar API Android
  • Django simple Captcha “No module named fields” error
  • Needing to do .toArray() to get output of mongodb .find() on key name not value
  • Xcode 4 NSLog Macro link in Xcode 3
  • Very simple C++ DLL that can be called from .net
  • Python urlparse: small issue
  • Asynchronous UI Testing in Xcode With Swift
  • If I include Java 8 in my Android app does that affect which devices it will work on?
  • Why is an OPTIONS request sent to the server?
  • FB SDK and cURL: Unknown SSL protocol error in connection to graph.facebook.com:443
  • Using $this when not in object context
  • Display issues when we change from one jquery mobile page to another in firefox
  • Deselecting radio buttons while keeping the View Model in synch
  • C# - Serializing and deserializing static member
  • How do I fake an specific browser client when using Java's Net library?
  • How reduce the height of an mschart by breaking up the y-axis
  • Bug in WPF DataGrid
  • Incrementing object id automatically JS constructor (static method and variable)
  • Perl system calls when running as another user using sudo
  • JSON with duplicate key names losing information when parsed
  • Symfony2: How to get request parameter
  • ORA-29908: missing primary invocation for ancillary operator
  • Jquery - Jquery Wysiwyg return html as a string
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass