Representing Derived Tables in XML¶

To represent derived tables, use the jdbcQuery element. This element is very similar to the jdbcTable element for tables, but contains an additional query element, used to represent the query for the derived table. jdbcQuery is a child of resources.

Table Hierarchy¶

The following hierarchy is used for jdbcQuery elements.

<jdbcQuery>
    <fieldList> (1)
        <field> (1...n)
    <filterString> (0...n)
    <query> (1)

jdbcQuery¶

The jdbcQuery element represents a derived table that results from an SQL query. jdbcQuery is similar to the jdbcTable element for a table, but has an additional child, query, used for the derived table query. jdbcQuery is a child of resources.

XML Attributes¶

Child Elements¶

fieldList¶

The fieldList is a container for the field elements in the table. When the derived table is created in the Domain Designer, the set of columns corresponds to the selection of columns in the query result on the Derived Tables tab.

Child Elements¶

field¶

The field element represents in the results of the query. A field element of a derived table must reference a column that is returned by the query. Only the columns represented by a field element are available for reference by other elements.

XML Attributes¶

query¶

The query element contains the SQL query that is sent to the database server. You can use any valid SQL that returns results, as long as the tables and columns in the query exist in the data source, and the columns in the result match the id and type of all field elements of the derived table given in the fieldList. SQL queries for a derived table are with respect to the JDBC driver for the data source. The syntax for a valid SQL query does not include a closing semi-colon.

You can also use JasperReports Server attributes in queries. See Using Server Attributes in Design Files for more information.

In many cases, the easiest way to create a derived table in the XML design file is to create the initial derived table in the Domain Designer and then export the file. When you create a derived table in the Domain Designer, it runs the query and generates columns based on the result set. These columns are present in the export file.

Example¶

The following sample query in PostgreSQL selects some columns from the result of a join and orders the results. One of the columns includes a field calculated in the SQL.

Only fields selected in the query – in this case, exp_date, store_id, amount, currency, conv, and as_dollars – can be exposed as columns of the derived table. Fields not selected in the query cannot be referenced in the Domain design.

<query>
 select e.exp_date, e.store_id, e.amount, c.currency, c.conversion_ratio conv,
  amount * c.conversion_ratio as_dollars
 from expense_fact e join currency c
  on (e.currency_id = c.currency_id and date(e.exp_date) = c.date)
 order by e.exp_date
</query>

Using Derived Tables¶

A derived table provides an alternate way to create joins and calculated fields. Here are some things to keep in mind when deciding how to implement the Domain:

• Calculated fields created within derived tables may use any function call recognized by the RDBMS. Calculated fields created in the Domain using the dataSetExpression attribute of the field element are limited to the functions available in the DomEL language. See Domain Expression Language (DomEL) for more information.
• The Domain mechanism applies filters, aggregation, and joins to derived tables by wrapping the SQL in a nested query, which may be less efficient on some databases than the equivalent query generated for a non-derived table.