Using a Generated Query

When you select Generated query, data is extracted from an ODBC data source by using an SQL SELECT statement that is constructed by IBM® InfoSphere™ DataStage®. SQL SELECT statements have the following syntax:

SELECT clause FROM clause
    [WHERE clause]
    [GROUP BY clause]
    [HAVING clause]
    [ORDER BY clause];

After you specify the tables to use and the columns to be output from the ODBC stage, the SQL SELECT statement is automatically constructed and can be viewed by clicking the View SQL tab on the Outputs page.

For example, if you extract the columns Name, Address, and Phone from a table called Table1, the SQL statement that is displayed on the View SQL tab is:

SELECT Name, Address, Phone FROM Table1;

The SELECT and FROM clauses are the minimum clauses that are required. These clauses are automatically generated by InfoSphere DataStage. However, you can use any of these SQL SELECT clauses:

SELECT clause

Specifies the columns to select from the database.

FROM clause

Specifies the tables that contain the selected columns.

WHERE clause

Specifies the criteria that rows must meet to be selected.

GROUP BY clause

Groups rows to summarize results. See Aggregating Data for a description of how this clause is used.

HAVING clause

Specifies the criteria that grouped rows must meet to be selected. See Aggregating Data for a description of how this clause is used.

ORDER BY clause

Sorts selected rows.

If you want to use the additional SQL SELECT clauses, you must specify them on the Selection tab on the Outputs page. The Selection tab is divided into two parts:

• WHERE clause. This text box allows you to insert an SQL WHERE clause to specify criteria that the data must meet before being selected.
• Other clauses. This text box allows you to insert a HAVING clause or an ORDER BY clause.