Originally published on 07-21-2011 06:34 AM
The SQL Query operator allows you to execute any SQL SELECT statement (not a stored procedure) against a database. The advantages of using this operator in place of the Read Table operator is that you can specify exactly which columns you want to retrieve, you can have the RDBMS execute multi-table joins, you can group and order the result set as desired, and you can use any of the aggregating and other functions that are supported by the RDBMS. In order to use this operator, you enter the SQL SELECT statement when creating the Schema artifact; Expressor then creates a Schema that corresponds to the result set.
To create a Schema based on a SQL SELECT statement, use one of the following approaches to start the Schema wizard.
- Select the New Schema > New Query Schema button in the Artifacts grouping on the Home tab of the ribbon bar.
- In the Workspace Explorer panel, highlight the project, right-click, and select New Schema > SQL Query Schema from the popup menu.
- In the Workspace Explorer panel, highlight the Schemas subdirectory under the project, right-click, and select New > SQL Query Schema from the popup menu.
In the wizard's first window, select the Database Connection for the desired database. If you do not yet have a suitable connection, click the New Database Connection... link to start the New Database Connection wizard. The Database Connection is unique to a RDBMS installation on a specific server computer.
In the wizard's second window, enter your SQL SELECT statement.
You must validate the statement; in this step the wizard connects to the database and confirms that the statement has the proper syntax for the selected RDBMS. Note in the following SQL SELECT statement the multi-table join and the use of the aggregate functions count and sum.
In the following window, the wizard shows the metadata it has extracted from the database, and in the final wizard window, you supply a descriptive name for the Schema artifact.
Once you have the Schema, you can open it in the Schema Editor, which allows you to edit and revalidate the SQL Statement or modify the associated composite type, for example, by specifying constraints and/or mapping formats.
To use the SQL Query operator, simply drag the shape onto the dataflow and specify the appropriate Database Connection and Schema Artifacts.