A join is represented in the design file as a jdbcTable element. A join tree is a child of the resources element.
| | The jdbcTable element is also used to represent tables from the data source. See Representing Tables in XML. |
Element Hierarchy for a Join Tree
The following hierarchy is used to represent a join tree.
(1) (1...n) (0...n) (1) (1) (1...n) (0...1) (1) (1...n)[/code] |
jdbcTable
The jdbcTable element is also used to represents a join tree, that is, the results of one or more joins between tables. There is one jdbcTable element for each join tree. jdbcTable is a child of resources.
| | jdbcTable can also be used to represent a table. See Representing Tables in XML for more information. |
XML Attributes
| Attribute | Type | Description | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| id | String | (Required) Unique identifier for the join tree in the Domain design. In the Domain Designer, each join tree is automatically given the ID JoinTree_n, where n is a sequential number. In the design file, you can give the join any name, as long as it is unique among all tables and derived tables. The id XML attribute can contain alphanumeric characters along with any combination of the following: @#$^`_~? It cannot start with a digit. | ||||||||||||
| datasourceId | String | (Required) Alias that identifies the data source for the Domain. 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. | ||||||||||||
schemaAlias | Name of the database schema for the first table in the join tree. Required if the data source uses schemas.
| |||||||||||||
| datasourceTableName | String | (Required) Literal name of the first table in the join tree. |
| | If you use the jdbcQuery element to define derived tables, you may have additional join trees in your Domain. |
The Domain Designer automatically exposes all columns of all tables in a join, but in the design file you need to specify only those columns you want to reference elsewhere in the Domain.
Child Elements
| Element Name | Description |
|---|---|
| <fieldList> | (Required) A container for the field elements in the join tree. A jdbcTable 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 join tree, the expression refers to columns using the table_ID.field_name form of the column ID. See Representing Pre-filters in XML for more information. |
| <joinInfo> | (Required) Gives the table ID and alias for the table specified by the schemaAlias and datasourceTableName attributes. The table ID and alias are used as the first table in the join definition. This element and its two attributes are required even if they are identical. |
| <joinList> | (Required) Container for the join elements. A jdbcTable element can contain only one joinList element. The left join in the first join in the joinList must be the table specified by the schemaAlias and datasourceTableName attributes of jdbcTable. |
| <joinOptions> | Specifies options for the join tree that influence how the joins that are pushed down to SQL in an Ad Hoc view. |
| <tableRefList> | (Required) Container for the list of tables and aliases used in the join. Must include a tableRef tag for every table used. A jdbcTable element can contain only one tableRefList element. |
fieldList
fieldList is a container for the field elements in the join tree. fieldList is a child of the jdbcTable element.
Child Elements
| Element Name | Description |
|---|---|
| <field> | (Required) Represents a column in the join tree. |
field
field represents a column in the join tree. When you export Domain from the Domain Designer, the design file includes every column in every table of the join. When you create your own design file, only the columns you want to reference are required. This includes fields that may not appear directly in the Domain, such as fields used in joins. field is a child of the fieldList element.
| | field elements are also used to represent calculated fields. Calculated fields still appear as children of |
XML Attributes
| Attribute | Type | Description |
|---|---|---|
| id | String | (Required) Identifier for the field, composed of the ID of the table in the design and the literal name of the column in the data source. The syntax is table_ID.field_name. 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, identical to the type in its table definition. |
joinInfo
joinInfo gives the table ID and alias for the table specified by the schemaAlias and datasourceTableName attributes of the jdbcTable element. This table is used as the first table in the join definition. The table named in joinInfo does not have a tableRef element.
Both attributes of joinInfo are required even if they are identical to the values in jdbcTable.
XML Attributes
| Attribute | Type | Description |
|---|---|---|
| alias | String | (Required) Alias for the table identified in referenceId; used in the expression attribute of the join element. By default, the alias is the same as the referenceID. If you use a distinct alias, you must be careful to use the alias throughout the join element that defines the join. |
| datasourceId | String | (Required) Alias for the data source for the Domain. 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. |
joinList
<joinList> is a container for join elements. There is a join element for each join in the join tree. The left attribute of the first join element in joinList must be the table specified by the schemaAlias and datasourceTableName attributes of jdbcTable.
Child Elements
| Element Name | Description |
|---|---|
| join | (Required) Representation of a join statement. |
join
The join element represents a single SQL join statement. The left and right tables, join type, join expression, and optional weight are specified as attributes. Every join in the join tree must have a separate join element.
left="left_table_ID" right=right_table_ID" type="join_type" expr="expression"
| | When you add or modify joins in the Domain design file, make sure that all tables in a join element are actually connected. If you include a table that is not actually joined to any other tables, the unjoined table will be included in any Ad Hoc view that uses that data island. In this case, the fields in the unjoined table will show up in the Ad Hoc view. You will receive errors in your Ad Hoc view when you add unconnected fields from a poorly-configured join. |
XML Attributes
| Attribute | Type | Description | ||
|---|---|---|---|---|
| left | String | (Required) Identifier for the first table in the join definition. This table must have been declared previously in the jdbcTable element. The declaration can appear either in the <joinInfo> element or in another join element that precedes this join element in the joinList. | ||
| right | String | (Required) Identifier for the second table in the join definition. | ||
| join_type | String | (Optional, defaults to inner). One of inner, rightOuter, leftOuter, or fullOuter.
| ||
| expr | String | Expression that compares the columns on which the join is made. See Options for the expr Attribute for more information. | ||
| weight | Integer > 0 | Integer that specifies a weight to use when calculating the minimum join path. You can assign a weight to one, several, or all of the join elements in a join set that has suppressCircularJoins enabled. Higher weights indicate costlier, less-desirable joins. When suppressCircularJoins is set to true, weight is ignored. |
Options for the expr Attribute
The expr XML attribute defines the expression used to compare the columns on which the join is made.
Each column used in the expression must come from the two tables in the current join element. When two columns are compared directly – for example, store.store_id == employee.store_id – the first column must come from the left table and the second column must come from the right table.
The following expressions are supported:
| • | Boolean operators – AND, OR, and NOT. See the other expressions for examples of how these are used. |
| • | comparison operators – Supports equal to (==), less than (<), less than or equal to (<=), greater than (>), greater than or equal to (gt;=), and not equal to (!=). Operators other than == must be used in conjunction with ==. For example: |
expr="(store.store_id == employee.store_id) AND
(store.coffee_bar != store.salad_bar)"
expr="(employee.employee_id == salary.employee_id) AND (salary.overtime_paid > 2)
AND (salary.overtime_paid <= 2.1)"
| | You have to use the character entities for less than (<) and greater than (>) |
| • | IN operator – Must be used in conjunction with ==. Supports the following: |
| • | a set of strings or values, separated by commas. Strings are enclosed in single quotes. For example: |
expr="(store.region_id == region.region_id) AND (store.store_city IN ('San Francisco','Portland', 'Seattle'))"
expr="(store.store_id == employee.store_id) AND (NOT store.coffee_bar IN ('true'))"
| • | a range of values, separated by a colon. For example: |
expr="(employee.employee_id == salary.employee_id) AND (NOT employee.salary IN (7000 : 8000))"
| • | joins between a table foreign key and a constant value. Must be used in conjunction with ==. For example: |
expr="(employee.employee_id == salary.employee_id) AND (employee.salary == 5000)"
Some join expressions, such as NOT, can not be edited in the Domain Designer. However they can be displayed. When you import the Domain into the Domain Designer, these joins are shown in read-only format. See Read-Only Joins for more information.
| | If you are using a field of type Date with an Oracle database, use the JasperReports Server Date() function in your join expression. For example: <join left="FOODMART_STORE" right="FOODMART_EMPLOYEE" type="inner" This applies to Date literals only; it is not necessary for fields with date formats but other types, for example, Timestamp. |
joinOptions
The joinOptions element allows you to influence the joins that are pushed down to SQL in an Ad Hoc view. A jdbcTable element can contain only one joinOptions element, which sets the options for the entire join tree. joinOptions lets you avoid circular joins in cases where there are N tables with N or more joins. See Join Tree Options for more information.
XML Attributes
| Attribute | Type | Description |
|---|---|---|
| suppressCircularJoins | Boolean | (Default = false) When this attribute is set to true for a join tree, an Ad Hoc view created from the join tree uses a minimum join. When suppressCircularJoins is set to true, you can optionally assign weights to the joins in the join set. When suppressCircularJoins is set to false, all joins in the join tree are used in Ad Hoc views. For most situations, the best practice is to set this option to true to avoid circular joins. Set it to false only if you need backwards compatibility with schema_1_0.xsd of the Domain syntax. |
| includeAllDataIslandJoins | Boolean | (Default= false) Available for backwards compatibility. When this attribute is set to true for a join set/data island, an Ad Hoc view created from the data island includes all joins specified for that data island in the Domain. This attribute can only be set to true when suppressCircularJoins is false. For most situations, the best practice is to set this option to false (default). |
tableRefList
The <tableRefList> element is a container for the list of tables and aliases used in the join tree. It must include a tableRef element for every table in the join tree.
Child Elements
| Element Name | Description |
|---|---|
| <tableRef> | (Required) Represents a table used in the join tree. |
tableRef
The <tableRef> element represents a table used in the join tree. There must be a tableRef element for every table in the join tree.
XML Attributes
| Attribute | Type | Description |
|---|---|---|
| tableId | String | The ID of a table within the design. |
| tableAlias | String | Alternative name for the table used within the join expression. |
| alwaysIncludeTable | Boolean | (Default = false) Setting this to true forces this table to be included in all Ad Hoc views created from this data island. For most situations, the best practice is to set this option to false (default). |
Join Tree Example
The following example shows a join tree with three joins between three tables. This example shows how you might use the following:
| • | To avoid circular joins, suppressCircularJoins is set to true. |
| • | The join between region and customer has a join weight of 2. This makes it a less desirable join than the other joins in the tree. |
| • | alwaysIncludeTable is set to true for the region table. |
[/code] [/code] <jdbcTable id="JoinTree_1" datasourceId="FoodmartDataSourceJNDI" schemaAlias="public" datasourceTableName="customer"> <fieldList> <field id="customer.account_num" fieldDBName="account_num" type="java.lang.Long" /> ... <fieldList> <joinInfo alias="store" referenceId="store" /> <joinOptions suppressCircularJoins="true"/> <tableRefList> <tableRef tableId="store" tableAlias="store"/> <tableRef tableId="customer" tableAlias="customer"/> <tableRef alwaysIncludeTable="true" tableId="region" tableAlias="region"/> </tableRefList> <joinList> <join left="store" right="customer" type="inner" expr="store.region_id == customer.customer_region_id"/> <join left="store" right="region" type="inner" expr="store.region_id == region.region_id"/> <join weight="2" left="region" right="customer" type="inner" expr="region.region_id == customer.customer_region_id"/> </joinList> </jdbcTable> [/code] [/code] [/code] |
Working With Joins
Keep the following in mind when working with join syntax:
| • | When you add or modify joins in the Domain design file, make sure that all tables in a join element are actually connected. If you include a table that is not actually joined to any other tables, the unjoined table will be included in any Ad Hoc view that uses that data island. In this case, the fields in the unjoined table will show up in the Ad Hoc view. You will receive errors in your Ad Hoc view when you add unconnected fields from a poorly-configured join. |
| • | When you use a join tag, the left table must have been defined previously in the jdbcTable element. You can define the table either in the joinInfo tag or as the right table in a previous join in the same joinList. Follow these guidelines for your joins: |
| • | When you have a join that depends on the right table from another join in the joinList, make sure that the dependent join appears after the join it depends on. |
| • | When you have a join that refers to a table that has not yet been defined, make sure that the new table is referenced as the right table in the join. |
| • | In some cases, you can start your Domain creation using the Domain Designer. To do this: |
| a. | Set up the rest of your Domain, such as derived tables and calculated fields, in the Domain Designer. |
| b. | Create a join that uses the same tables that you want to join in your Domain. This will set up the jdbcTable and fieldList tags for you. |
| c. | Export the schema from the Domain Designer. |
| d. | Manually edit the tableRefList and joinInfo sections of the exported design file to create the join you want. |
Tips for Using Joins
| • | To avoid join cycles in queries, set <joinOptions suppressCircularJoins=true/>. This will find the lowest weight join set. |
| • | To configure your Domain to use the fewest number of joins required to reach a given set of chosen fields, use <joinOptions suppressCircularJoins=true/> AND set all <join weight="W"> values to be the same value. In this case, the lowest weight join set will be a set that has the minimum possible number of joins. |
| • | You do not have to set the join weight explicitly. If the weight is not set, it defaults to 1. |
| • | To give preferential treatment to joins that involve indexed columns for better join performance, assign lower join weights to the preferred joins and higher join weights to less desirable joins. |
| • | To make sure that one or more specific table(s) always get included in your results, set alwaysIncludeTable=true. For example, to make sure tableA is always included, use |
<tableRef tableId=tableA tableAlias=tableA alwaysIncludeTable=true/>
Recommended Comments
There are no comments to display.