On the General tab of the Properties window for the advanced ODBC source, you can use the Query Builder window in Integration Manager to enter SQL information instead of typing in a SQL statement directly.
To view this window, open the properties window for the advanced ODBC source and from the General tab, choose Query Builder.
Use the fields at the top of the window to specify which rows are included in the query results.
To select specific rows from the table, you create selection criteria based on fields from the tables available for the query. You can use logical AND and logical OR operators to apply several criteria.
The LIKE operator allows you to perform basic pattern matching with string columns. You can use the percent sign (%) as a wildcard character, representing any sequence of characters.
You can select the fields that are included in the query results.
To specify the fields to include in the query results, select the appropriate tables in the Tables list. Then select the individual fields in the Fields to Show list. If you do not select any fields in the list, all fields for the selected tables are included.
If you select an item in the Group By list, the data returned by the query is grouped based on that field. For example, if you group by the City field, the rows that have the same value for City appear as a group.
To specify how the rows returned by the query are sorted, select an item in the Order By list. The rows returned by the query are sorted based on the field you select. You can sort items in ascending or descending order.
|
If you are creating a query relationship for this query, you can improve the performance of the integration by sorting the query results by the field used for the relationship. |
If the query returns data from more than one table, you must create joins between the tables to indicate how they are related. To create a join, select the two tables that are related and click Set Table Joins.
In the Join Tables window, select the two tables to join. Select the field or fields in each table that are related. The table join is based on these fields. Click Add Join to Query.
|
Rather than using joins, you may find it easier to use multiple queries and create query relationships between them in Integration Manager. |
To have the query only return a specified number of rows, supply a value indicating the number of rows to return. For example, the value 10 indicates that the first 10 rows that meet the query criteria are returned.
If you select the Top Percent check box, the specified percentage of rows is returned. For example, the following selections indicate that the first five percent of the rows that meet the query criteria are returned