Modifying the SQL Statement

InfoSphere® DataStage® allows you to edit the SQL statement on the SQL tab. Right-click to get a shortcut menu with editing functions such as cut, paste, and copy. If you have a predefined SQL statement you want to use, you can cut and paste it into the SQL text box. Job parameters cannot be used in the SQL statement; only column names and constants (except HIGH_VALUES and LOW_VALUES) are supported.

Click Verify to validate the statement when you are done. If the entire SQL SELECT statement can be parsed and table metadata has been defined in the repository for the identified tables, then your changes will be propagated back to the other pages where they apply.

If the entire SQL SELECT statement can be parsed, or at least the SELECT list clause and the SELECT FROM clause can be parsed, but no table metadata has been defined in the repository and no columns exist on the Columns tab, then InfoSphere DataStage creates column definitions for each column in the SELECT list using the specified column name, a default data type of Char, and length of 10. If columns already exist on the Columns tab, they must match the columns defined in the SELECT list, including the number of columns and attributes such as data type, length, and scale. You can use the Load and Clear All buttons to edit, clear, or load new column definitions on the Columns tab.

If the SELECT list on the SQL tab and the columns list on the Columns tab do not match, Teradata precompiler, Teradata bind, or execution time errors will occur.

If the SQL SELECT list clause and the SELECT FROM clause can be parsed and table metadata has been defined in the repository for the identified tables, then the columns list on the Columns tab is cleared and repopulated from the metadata. The Load and Clear All buttons on the Columns tab are unavailable and no edits are allowed except for changing the column name.

If either the SQL SELECT list clause or the SELECT FROM clause cannot be parsed, then no changes are made to columns defined on the Columns tab. You must ensure that the number of columns matches the number defined in the SELECT list clause and that they have the correct attributes in terms of data type, length, and scale. Use the Load or Clear All buttons to edit, clear, or load new column definitions on the Columns tab. If the SELECT list on the SQL tab and the columns list on the Columns tab do not match, Teradata precompiler, Teradata bind, or execution time errors will occur.

If validation is unsuccessful, you will receive an error message. You can correct the SQL statement, undo your changes, or choose to continue without making any corrections. If you continue without making corrections, however, only the General, SQL, and Columns tabs on the Outputs page will be active.