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

Attribute

 Type

Description

datasourceId

String

(Required) Identifier for the data source, as set in the id attribute of jdbcDataSource. When creating a design file, this alias may be any name you choose, but it must be identical for all tables and derived tables. When uploading the file, the datasourceId automatically becomes the alias associated with the data source defined for the Domain.

id

String

(Required) Unique identifier for the derived table in the Domain design file. The id of a derived table is used everywhere the id of jdbcTable is used. If you copy a derived table to join it multiple times, each copy must have a different id.

The id XML attribute can contain alphanumeric characters along with any combination of the following: @#$^`_~? It cannot start with a digit.

Child Elements

Element Name

 Description
<fieldList>

(Required) A container for the field elements in the derived table. A jdbcQuery element can contain only one fieldList element.
<filterString>

Expression that evaluates to true or false when applied to each row of values in the data source. For a derived table, the expression refers to columns using the field_name form of the column ID. See Representing Pre-filters in XML for more information.
<query> (Required) The SQL query sent to the database server.

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

Element Name

 Description
<field>

(Required) A column from the table specified in jdbcQuery.

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

Attribute

 Type

Description
id String

(Required) 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.

The id XML attribute can contain alphanumeric characters along with any combination of the following: @#$^`_~? It cannot start with a digit.
type String

(Required) The Java type of the column, as determined from the data source by the JDBC driver. The available types are shown in Supported Types.

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.

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.

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>

The preceding example is not valid for SQL Server. SQL queries for a derived table are resolved using the JDBC driver for the data source and the derived table becomes a subquery in the generated SQL. SQL Server requires a TOP or FOR XML clause in any subquery that uses ORDER BY.

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.