Implementation Guide for Oracle Self-Service E-Billing > Using the Reporting Engine > Core Reporting Features >

Dynamic SQL

Some situations require you to generate SQL dynamically. For example, you might have a report that searches the call details. One of the criteria is the call date. You want to search for call date equals a particular date, or you want to search for call dates between a start date and end date. Because the where clause is different for these two search cases, without dynamically generated SQL, you would be forced to write two reports with two SQL clauses. Dynamically generated SQL can solve this problem. The where clause of the SQL statement can be generated based on the current operation (equal or between), so only one report is required.

The Reporting Engine allows you to write an SQL query in a Velocity template, so that the SQL query will be parsed before it is executed. You must set the dynamic attribute of <query> to true. For example:

<query dynamic="true"> <![CDATA[
select * from my_table where #if ($equal) date = ? #else date >= ? and date <= ? #end
]]></query>

The variable $equal is set by the caller through the IreportActionCallback interface. It is true if the user selects the date equal operation, and false if the user chooses the date between operations.

NOTE:  The number of question marks is different based on operation types: one for equal and two for between. To solve this problem, the report engine supports binding a Collection object to question marks. The report engine loops through the Collection and binds each element to question marks.

The following example shows how to bind:

<inputBinding object="form" property="parameter(dateList)" />

The method form.getParameter(dateList) returns a list of Date objects, and each date in the list is bound to the question marks in the query. The caller of Reporting Engine is responsible for collecting the list of dates and passing them to ActionForm.setParameter(dateList, dateList). (This assumes that Action Bean is as map-backed form, and has a pair of setParmeter and getParameter methods).

Another common use case is to generate the in operation in a where clause. The number of question marks is based on the size of a Collection object.

For example, if you have a list of categories saved in a List, and want to generate a where clause, then use

Where category in (?,?,..,?,?)

In this clause, the number of question marks is the size of the List.

When doing the input binding, there is only one List, but loops through the List to set the question marks in the SQL as appropriate. This ensures that the number of question marks match the number of variables passed in.

There is a macro to help you generate the number of question marks based on the collection size:

#macro getSQLVariablesIgnoreNull($list $columnName)

The macro generates the list.size() number of question marks. For example:

select * from my_table where date in getSQLVariablesIgnoreNull($dateList "date")

If the dateList size is 2, and it is Oracle database, then the result is:

select * from my_table where date in (NVL(?,date), NVL(?,date))

In this clause, NVL means ignore this question mark if the value is null.