传递SQL运算符以通过iReport参数进行查询 [英] Passing SQL operators to query through iReport Parameter

问题描述

我正在尝试基于处理事务的oracle SQL查询在iReport中创建报告。所涉及的实际表格非常冗长，与问题无关。我正在尝试创建一个报表，当交易金额满足以下3个条件中的1个时，该报表将返回结果：

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:

• 介于1000.00和2499.99之间


• 介于2500.00和9999.99之间


• > = 10000.00

我正在尝试创建一个选择列表参数，运行报表的人可以在上述规范之一中进行选择。在我实际创建参数之前，我一直在尝试在SQL Developer中测试查询。我试图实现此问题的查询行是：

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

我的测试输入是介于1000和9999之间。当它在查询中被硬编码时，它工作正常，但是当我尝试通过此输入传入它时，我得到一个无效的关系运算符错误。

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?

推荐答案

尝试在查询中使用这样的方法：

select ...来自table1，其中transaction_amount $ P！{paramReport}

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

来自JasperReports终极指南：

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>

这篇关于传递SQL运算符以通过iReport参数进行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！