Using a Generated Query

When you select Generated query, data is extracted from a UniVerse data source using an SQL SELECT statement constructed by InfoSphere® DataStage®.

SQL SELECT statements have the following syntax:

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

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

Note: The View SQL tab appears only when you select Generated query on the General tab on the Outputs page.

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

SELECT Name, Address, Phone FROM Table1;

The SELECT and FROM clauses are the minimum required and 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 containing 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 or an ORDER BY clause.