Using Generated Queries

By default, IBM^® WebSphere^® DataStage^® extracts data from an Informix^® data source using an SQL SELECT statement that it constructs. The SQL statement is automatically constructed using the table and column definitions that you entered in the stage output properties.

When you select Generated SQL query, data is extracted from an Informix database using an SQL SELECT statement constructed by the stage. 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 Informix CLI stage, the SQL SELECT statement is automatically constructed and can be viewed by clicking the SQL tab on the Output page.

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

SELECT Name, Address, Phone FROM Table1;

The SELECT and FROM clauses are the minimum required and are automatically generated by the stage. 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.
HAVING clause. Specifies the criteria that grouped rows must meet to be selected.
ORDER BY clause. Sorts selected rows.

If you want to use the additional SQL SELECT clauses, you must enter them on the Selection tab on the Output page. These clauses are appended to the SQL statement that is generated by the stage. If this link is a reference link, only the WHERE clause is enabled.

The Selection tab is divided into two areas (panes). You can resize an area by dragging the split bar for displaying long SQL clauses.

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 GROUP BY, HAVING, or ORDER BY clause.