expressor 2.3 includes a large collection of connectors (motors) for relational, flat-file, xml, SAS files, Cobol-copybook, and other source and target data formats. This blog entry is about explaining to you what our in-sql motor in expressor 2.3 is all about.
Our in-table motor executes the equivalent of the statement SELECT * FROM table_name, where * is replaced with the table column names included in the image file. If desired, you can specify a WHERE clause as part of a manually entered database URI, but in this case the same WHERE clause would be applied to all channels in a parallel network. This is most likely not what you really want.
If you need to execute a more involved SELECT statement or a stored procedure, perhaps with a table join, nested statement, or complex partitioning directive across multiple channels, you would use the in-sql motor. With this motor, the statement you want to execute is included in the image file, although if used with a parallel network the part of the WHERE clause that specifies the partitioning directive will be contained within the channel specification. Let’s see how this all comes together.
Suppose you want to read the names of a subset of individuals who were President of the United States in the nineteenth, twentieth, and twenty-first centuries. The SELECT statement (the following examples use syntax specific to Microsoft SQL Server) might look something like the following.
SELECT last_name, first_name FROM presidents WHERE political_party IN
(SELECT party FROM party_list)
AND date_of_inauguration > CAST( ’1800-01-01′ AS datetime )
This type of compound statement could not be executed by the in-table motor but is easily implemented by the expressor in-sql motor.
Now suppose you want to return members of the Democratic and Republican parties on separate channels. You would add a partitioning directive to the nested SELECT statement. The following fragment shows the statement that would be included in the image file.
SELECT last_name, first_name FROM presidents WHERE political_party IN
(SELECT party FROM party_list <partitioning_directive>)
AND date_of_inauguration > CAST( ’1800-01-01′ AS datetime )
The entry <partitioning_directive> is a placeholder for a WHERE clause that will be used to partition the result set across the channels. In this case, the partitioning directive would be specified through the value assigned to the channel query attribute. One channel in the associated network file would include
query=”WHERE political_party = ‘Democratic’ “
and the second channel would include
query=”WHERE political_party = ‘Republican’ “
In this example, the nested SELECT statement did not include the WHERE keyword, so this keyword must be included in the value assigned to the channel’s query attribute. But that may not always be the case. The value assigned to a channel’s query attribute might be an extension to a WHERE clause contained in the nested SELECT statement.
For example, suppose you want to retrieve only Democratic presidents partitioned on date of inauguration. The SELECT statement embedded in the image file might look something like the following.
SELECT last_name, first_name FROM presidents
WHERE political_party=’Democratic’ <partitioning_directive>
And the partitioning directives in the two channels could have the following content.
AND (date_of_inauguration > CAST( ’1950-01-01′ AS datetime )
AND (date_of_inauguration < CAST( ’1950-01-01′ AS datetime )
Since the nested SELECT statement includes the WHERE keyword, the query attribute’s content simply extends the WHERE clause with the AND keyword.
As you can see from these examples, the in-sql motor gives you full control not only over the complexity of the SELECT statement but what portion of the SELECT statement contains the partitioning directive that distributes records across the channels in a parallel network.
John Lifter, expressor
