Using a User-Defined SQL Statement

you can specify your own SQL statement for each UniVerse output link instead of using the SQL statement constructed by InfoSphere® DataStage®.

To specify an SQL statement:

1. Click the User-defined SQL query option on the General tab on the Outputs page. The SQL Query tab appears.
2. Click the SQL Query tab. When you first view this tab, the SQL for primary inputs and SQL for reference inputs fields might contain the SQL statements constructed by InfoSphere DataStage. These are displayed if you selected Generated query before selecting User-defined SQL query. You can modify or overwrite each statement to construct your own SQL query or call to a stored procedure.

   The entries in these fields depend on whether the output is a primary input to a stage or a reference input to a Transformer stage:

   • If the output is a primary input to any stage, whether or not it is a Transformer stage, edit the SQL for primary inputs field. The SQL query must contain the same number of columns (and column names) as the SQL statement constructed by InfoSphere DataStage.

     You must ensure that the table definitions for the output link are correct and represent the columns that are expected. The result set generated from this statement returns at least one row.

     Note: If more than one result set is produced, only the first set is used.
   • If the output is a reference input to a Transformer stage, edit the SQL for reference inputs field. The SQL query must contain the same number of columns as the SQL statement constructed by InfoSphere DataStage. You must ensure that the table definitions for the output link are correct and represent the columns that are expected. The statement must have the same number of parameter values (?) as key columns on the link. The result set generated by this statement or procedure contains at most one row.
3. Click OK to close theUniVerse Stage dialog box. Changes are saved when you save your job design.