|
|
|
|
|
|
|
|
|
|
- Overview
- How to?
- Adding a join into the current select query
- Deleting a join from the current select query
- Notion of external join in the current select query
- Editing the conditions of a join in the current select query
- Adding a condition
- Limitations and notes
- Specific joins
- SQL Server syntax for joins
- Advanced parameters
Joins between the data files of a select query
When several data files are used in a query, the query editor automatically searches for the links between these files described in the analysis. These links are called joins in the query editor. For example, the following query selects the numbers of the orders placed by the customers who live in Paris. The ORDERS and CUSTOMER data files are joined by the CustomerNum item. The query editor automatically defines the joins between these two data files.
Note Data file bindings appear in the graphical representation of the query only if both linked data files are used by the query. By default, the joins are automatically created by the query editor. However, these joins may not correspond to the desired result.. Therefore, you have the ability to add or delete joins. These operations are described in the following paragraphs. Adding a join into the current select query To add a join to the current select query: - Right click the join and select "Edit join" in the context menu. The description window of the query joins appears.
- Click the "Add a join" button. A window used to define a new join is displayed.
- Specify the characteristics of the join to be added.
- Validate. The join is automatically added to the query.
This gives you the ability to create multiple joins between two data files. Deleting a join from the current select query To delete a join from the current select query: - Right click the join and select "Edit join" in the context menu. The description window of the query joins appears.
- Select the join to delete.
- Click the "Delete join" button. The join is automatically deleted. The joins between data files are automatically optimized in order for the query to operate properly.
Notion of external join in the current select query When calculating a join between two data files ("Parent" data file and "Son" data file), by default only records with a match in both data files are retained. However, the notion of "External join" allows you define a rule to include: - records in the "Parent" data file which have no match in the "Son" data file.
- records in the "Son" data file which have no match in the "Parent" data file.
To define an external join in the current select query: - Right click the join and select "Edit join" in the context menu.
- The description window of the query joins appears.
- Select the join to modify.
- Two options are available on this join, allowing you to define an external join ("Include also").
- Select the option(s) corresponding to the desired result.
- Validate.
Editing the conditions of a join in the current select query To edit the conditions of a join in the current select query: - Right click the join and select "Edit join" in the context menu.
- The description window of the query joins appears.
- Select the join to edit.
- Click the "Edit join conditions" button. The edit window of conditions is displayed.
- You can:
- Add a new condition.
 - Modify a condition.
 - Delete a condition.
 - Define the logical sequence of conditions via operators (AND, OR) and brackets ( and ). You must use the number representing the condition in the formula. For example, if there are 2 conditions, the formula could be: "1 OR 2".
Adding a condition To add a condition: - Click . The window for creating a condition is displayed.
- Select the item for which the condition will be created (1). Only the "key" items are proposed.
- Select the operation to perform in the list (2).
- Select the type of element to compare for the operation to perform (3):
- the value: this option is selected in the case of a static (hard-coded) value in the query.
- parameter: this option is selected if parameters are passed during query execution.
- the field: this option is selected in the case of a comparison with a field in another data file in the query. Only the "key" items are proposed.
- Validate. The condition is added to the previous condition.
- Don't forget to check the logical sequence of created conditions.
Specific joins The joins such as (A Join B on x=y) Join C on y=z are not supported. Advanced parameters By default, the joins use the ISO syntax (INNER JOIN / LEFT OUTER JOIN / RIGHT OUTER JOIN / FULL JOIN). You also have the ability to use the Oracle syntax of joins. However, this syntax compatible with Oracle 7 is not recommended. To use the Oracle syntax: - Open the query description window.
- Click the "Advanced" button.
- On the "Joins" tab, select the requested type of syntax.
This page is also available for…
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|