Dynamic and custom SQL

[danielobs]

Hi,

It is possible to made in Ireport the "dynamic" SQL.

In JasperServer I have parameter "fields" (java.util.Collection), which contains a numbers of fields which are to be use in the SQL to aggregation of data.

When in filds parameter is : "1","2","3" the SQL should look like:
SELECT P1 as NAME1, P2 as NAME2, P3 as NAME3, SUM(PRICE) FROM TABLE_1 GROUP BY P1, P2, P3

and when in filds parameter is : "1","2" the SQL should look like:
SELECT P1 as NAME1, P2 as NAME2, SUM(PRICE) FROM TABLE_1 GROUP BY P1, P2

and next examples : "1","4" = SELECT P1 as NAME1, P4 as NAME2, SUM(PRICE) FROM TABLE_1 GROUP BY P1, P4

and next examples : "4","1","3" = SELECT P4 as NAME1, P1 as NAME2, P3 as NAME3, SUM(PRICE) FROM TABLE_1 GROUP BY P4, P1, P3

Dynamic text is needed after SELECT and after GROUP BY.

It is posible made text of SQL dynamically according input parameters?


Best regards,


Daniel

[djsiders]

A few things I have done for odd SQL like this:

$P!{PARM_NAME} put the literal text of the param into the SQL (the ! is the key) instead of using it like a parameter value

In your case, since your param is a colletion, possibly something like $P!{PARM_NAME}.get(0)

If you don't know the number of items in your collection ahead of time, you can use a Scriptlet to create the actual SQL.  In that case, create a param with a default value like:

$P{REPORT_SCRIPTLET}.createSQL($P{YOUR_COLL_PARAM})

and then in your reports query definition, simply use

$P!{PARM_NAME} as your entire query.  At runtime, the query will be the text the Scriptlet code generates.

The scriptlet code would accept your collection param as an argument, and step through creating the dynamic SQL as required.