Building SELECT statements

Build SELECT statements to query database tables and views.

Procedure

  1. Click the Selection tab.
  2. Drag any tables you want to include in your query from the repository tree to the canvas. You can drag multiple tables onto the canvas to enable you to specify complex queries such as joins. You must have previously placed the table definitions in the IBM® InfoSphere® DataStage® repository. The easiest way to do this is to import the definitions directly from your relational database.
  3. Specify the columns that you want to select from the table or tables on the column selection grid.
  4. If you want to refine the selection you are performing, choose a predicate from the Predicate list in the filter panel. Then use the expression editor to specify the actual filter (the fields displayed depend on the predicate you choose). For example, use the Comparison predicate to specify that a column should match a particular value, or the Between predicate to specify that a column falls within a particular range. The filter appears as a WHERE clause in the finished query.
  5. Click the Add button in the filter panel. The filter that you specify appears in the filter expression panel and is added to the SQL statement that you are building.
  6. If you are joining multiple tables, and the automatic joins inserted by the SQL builder are not what is required, manually alter the joins.
  7. If you want to group your results according to the values in certain columns, select the Group page. Select the Grouping check box in the column grouping and aggregation grid for the column or columns that you want to group the results by.
  8. If you want to aggregate the values in the columns, you should also select the Group page. Select the aggregation that you want to perform on a column from the Aggregation drop-down list in the column grouping and aggregation grid.
  9. Click on the Sql tab to view the finished query, and to resolve the columns generated by the SQL statement with the columns loaded on the stage (if necessary).