Select statement

Use this property to specify the SQL statement to select rows from an existing table.

This property is available only if you set Generate SQL to No.

Click the browse button to open a statement window in which to type your SQL statement. Click Tools, then select Build New SQL to open the SQL builder, where you can graphically build, validate or generate your SQL statement. Both of these buttons are available only after you click inside this property value.

Valid values are determined by the specific database.

Subtotal statement usage

To use a subtotal in your SELECT statement, you must add a subtotal column to your schema on the Columns tab. For example, say that you want to use the following SELECT statement:
Select Title, sum(Age) AS subtotal from YOUR_TABLE GROUP BY Title 
HAVING SUM(Age) >60 ORDER BY subtotal
You must define Title and subtotal as your column names on the Columns tab. When the connector executes this statement, Age is replaced by AS subtotal.

LOB column usage

Some ODBC drivers, such as Microsoft SQL Server drivers, have a limitation when you are working with large object (LOB) columns. With the SQL Server drivers, if a select list contains a LOB column, the LOB column must be listed last in the select list. For these drivers, there are two different scenarios where the query fails:
  • The LOB columns are not listed last.
  • You specify a wildcard asterisk (*) for the select list and the position of the LOB column in the table is not the last column in that table.

When you use drivers that have this limitation and you have LOBs in your table, you must ensure that the LOB columns are listed last in the select list.

In this example, you have two LOB columns in your Employees table: Description and Comments. If you write the following SELECT statement and run your job, the job fails:

select EmployeeID, Description, Comments, Photo from Employees

This is because the select list includes the Photo column after the LOB columns. Edit this statement to move the LOBs to the end as specified in the following statement:

select EmployeeID, Photo, Description, Comments from Employees

The LOB columns are now at the end of the statement. The job will run successfully.