Representing Derived Tables in XML

This documentation is an older version of JasperReports Server User Guide. View the latest documentation.

Representing Derived Tables in XML

Derived tables are similar in structure to tables, but they use the jdbcQuery element that contains the query element:

•

jdbcQuery – Represents a derived table that results from an SQL query. Both attributes of jdbcQuery are required:

•

datasourceId – Alias that identifies the data source of the Domain. In the design file, this alias must be identical for all tables and derived tables.

•

id – Table ID used to reference the derived table in the Domain design. Any reference to the id of a jdbcTable may also reference the id of a derived table.

•

fieldList – A required container of the field elements.

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.

•

field – Represents a column in the results of the query.

Only the columns represented by a field element are available for reference by other elements. The columns of a derived table must be among those returned by the query.

•

id – Literal name of the column in the query result. If the query gives an alias to the column in a SELECT AS statement, the id is the same as the alias. The id must be unique within the query results, but not necessarily within the Domain.

•

type – The Java type of the column, as determined from the data source by the JDBC driver.

•

query – The SQL query 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. The syntax for a valid SQL query does not include a closing semi-colon). If you add a derived table in the Domain Designer, it runs the query and generates columns based on the result set. You can then export the design file containing the generated column list.

SQL queries for a derived table are with respect to the JDBC driver for the data source. If you are working with a virtual data source, SQL queries are validated against Teiid SQL, which provides DML SQL-92 support with select SQL-99 features. For more information, see the Teiid Reference Guide under the Documentation link on the Jaspersoft Support Portal.

The following sample query selects some columns, including a field calculated in the SQL, from the result of a join with sorted results. In this case, only exp_date, store_id, amount, currency, conv, and as_dollars can be exposed as columns of this derived table.

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

•

Unlike joins defined in the Domain, joins within a derived table are not restricted to equality comparisons when uploaded to the Domain Designer. For more information, see Representing Joins in XML.

•

Unlike calculated fields in DomEL, calculated fields within derived tables may use any function call recognized by the RDBMS. See The DomEL Syntax for restrictions on function calls in calculated fields.

•

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.