Column aliases

When the DRS Connector stage is configured for the IBM® DB2® and ODBC database types, the items that are specified in a SELECT statement must correspond to the column names on the output link of the stage.

For example, for SQL expressions in the select list the use of column aliases is required since the actual text of the expressions will not match the column names on the link. This applies to the native SQL expressions specific to the database type for which the stage is configured as well as to the SQL meta tags supported by the DRS Connector stage.

When a Dynamic RDBMS stage is migrated to a DRS Connector stage that is configured for IBM DB2 or ODBC database type and the migrated stage specifies SELECT statement which does not use aliases for the SQL expressions in the statement, you must add column aliases to the expressions so that they correspond to the column names on the link.

The DRS Connector stage configured for the Oracle database type and the Dynamic RDBMS stage allow the use of SELECT statements where items in the list do not match column names on the output link. This includes the case when SQL expressions are used without column aliases. Note though that the use of column aliases is highly recommended in this case as well.

For example, if the DRS Connector stage is configured to read data from an IBM DB2 or ODBC database type, and the output link of the stage contains columns C1, C2 and C3, the following statement with SQL meta tags will not work:

SELECT %ABS(C1), %MIN(C3), %MAX(C2) FROM TABLE1

For this statement to work, aliases would need to be provided for the SQL expressions used in the statement and those aliases would need to correspond to the column names on the link with which the SQL expressions should be associated.

For example the following statement would associate the expression %ABS(C1) with the column C1 on the link, the expression %MIN(C3) with the column C3 on the link and the expression %MAX(C2) with the column C2 on the link:

SELECT %ABS(C1) C1, %MIN(C3) C3, %MAX(C2) C2 FROM TABLE1

For the DRS Connector stage configured for Oracle database type and for the Dynamic RDBMS stage the statement without aliases will work and the stage will perform association between the expressions and the columns on the link in an ordered fashion: the expression %ABS(C1) will be associated with the column C1 on the link, the expression %MIN(C3) will be associated with the column C2 on the link and the expression %MAX(C2) will be associated with column C3 on the link. In some cases such an association strategy can be intended, but in other cases it may not be. To prevent the stage from performing association in the ordered fashion it is highly recommended to explicitly include column aliases that correspond to the names of the columns on the link, so that the stage can perform the association by name.