Creating a Packaged Query
To create a packaged query
1. Right-click an appropriate location in the resource tree to add the packaged query, and select New Packaged Query.
The New Package Query window opens.
2. Type a name for the packaged query.
3. Select the data source with which to associate the packaged query.
If you want to associate the packaged query with a different data source later on, you can use the rebinding technique, which is described in the section
Rebinding a Procedure.
4. Click OK.
The editor opens on the right, displaying the SQL panel.
Use the SQL panel to formulate and edit the SQL for the packaged query. The parameters you define in the SQL panel must match the parameters designed in the Parameters panel, including the order in which they are provided.
5. In the SQL panel for the packaged query:
a. If the database-specific query already exists in the file system, upload the file using Insert from file in the editor’s toolbar.
The SQL from the file is copied onto the SQL panel.
b. If the database-specific query is not available elsewhere, type it in the SQL panel. Add the string “<version 2>” to the beginning of the first SQL line to handle null values passed by input parameters and to automatically escape any single-quote characters that might appear in the substitution values of any string or TIMESTAMP input parameters.
Packaged Query SQL does not require full pathnames to resources because the packaged query is already associated with the data source.
The packaged query must have exactly one output parameter that is a cursor with at least one column. In the following, the data source is orderdetails (specified in the FROM clause).
Define input parameters with curly braces, {N}, enclosing numerals that start from zero to N, as the input parameter placeholders (in this example specified in the WHERE clause). For details on defining input parameters and examples, see
Specify Input Parameters for a Packaged Query.
Note: If the packaged query is equivalent to a single SELECT statement with a table or cursor output, check the Single Select check box. This flag tells the TDV query engine that the packaged query can be treated as a derived table, potentially enhancing the efficiency of the query plan. Because TDV does not process the packaged query, the user must make the determination whether the output qualifies for this optimization. Marking the packaged query as a Single Select allows TDV to add predicates (filter conditions) to the packaged query so that it can be a target of a semijoin optimization.
6. Click the Parameters tab to display the Parameters panel.
Use the Parameters panel to design the input and output parameters for the query. The parameters defined in the Parameters panel must match the parameters defined in the SQL panel, including their order. After all the parameters are specified, you can use them to do a top-down design of the SQL in the SQL panel.
To proceed with the example in
Creating a Packaged Query, add one output parameter (UnitPrice) and two input parameters (ProductID and Status) in the
Parameters panel.
a. Select the output parameter named result, and click Add.
b. Select Decimal > DECIMAL as the data type.
A new parameter is added with the specified data type.
c. Rename the newly added parameter as UnitPrice (to indicate the output), and click ENTER.
d. Make sure that this parameter is a part of the CURSOR output by moving it to the right of result using the right-arrow button, if necessary.
e. Add another parameter of the type Integer > INTEGER.
This is the first input parameter in our example. If it is placed under result as a part of the CURSOR output, use the left triangle button on the toolbar to move it outside the output cursor. A right-arrow icon appears next to it.
A right-arrow icon denotes an input parameter; a left-arrow icon denotes an output parameter.
f. Rename the parameter as ProductID, and click ENTER.
g. Add another input parameter named Status of the type String > CHAR.
7. Save the packaged query.
8. Execute the packaged query.
Executing a packaged query is similar to executing a parameterized query. For details, see
Executing a Parameterized Query.