This type of query allows you to combine the result of two distinct queries. Compound queries can be one of two types, JOIN and UNION.
A JOIN compound query merges the result of two queries, using a specified set of keys. You can specify one of four join types: Inner, Left Outer, Right Outer, Full Outer. The result of this join will contain the columns of both queries if they are of the same type. Both the left and right side queries must be identified by an ID. You must also specify which keys (column IDs on the source queries) are used to join the data. This data source has the following properties:
| Property | Description |
|---|---|
| Name | The name of the compound query. |
| Left | The first query. |
| Right | The second query. |
| Parameters | Lists the parameters' name, default value (i.e., the default value if the parameter value is not specified when the data access is called), and type (String, Integer, Numeric, Date, StringArray, IntegerArray, NumericArray, and DateArray) which are passed on to the compound query. |
| Calculated Columns | The columns to be calculated by a given formula. Each calculated column requires two properties: Name (the name that will be output by CDA), and Formula (the column's definition itself). Formulas are written in Open Formula format. |
| Columns | Names of the columns, in case you want to rename a particular column. |
| Left Keys | The ID or IDs of the columns from the first query which are common to the second query. |
| Output Columns | The IDs of the columns which will be the output from both queries in order, starting with the columns from the left query and then the columns from the right query. |
| Output Mode | The column's output mode, which will include or exclude the columns set above. |
| Right Keys | The ID or IDs of the columns from the second query which are common to the first query. |
| Join Type | The join type to be used, such as Inner, Left Outer, Right Outer, or Full Outer. |
A UNION compound query takes the results of two queries with the same number of columns and returns the compounded result set from both queries. A union query data source has the following properties:
| Property | Description |
|---|---|
| Name | The name of the compound query. |
| Top | The ID of the query which will stay on top. |
| Bottom | The ID of the query which will stay on the bottom. |
| Parameters | Lists the parameter's name, default value (i.e., the default value if the parameter value is not specified when the data access is called) and type (String, Integer, Numeric, Date, StringArray, IntegerArray, NumericArray, and DateArray) which are passed on to the compound query. |
| Calculated Columns | The columns to be calculated by a given formula. Each calculated column requires two properties: Name (the name that will be output by CDA), and Formula (the column's definition itself). Formulas are written in Open Formulaformat. |
| Columns | Names of the columns, in case you want to rename a particular column. |
If the columns on both data sets have different names, the name of the column in the top result set will be used in the union’s resulting data set.