Adding a Parameter to a FROM Clause
The following steps use a transformation as a sample procedure type to include in a parameterized query. The steps are similar for other types of procedures.
To include a parameter in the FROM clause of a parameterized query
1. Right-click a node in the resource tree and select New Parameterized Query.
2. In the Input window, supply a unique name for the parameterized query and click OK.
The editor opens in the right pane.
3. Drag the transformation that requires input parameters and drop it onto the
Model panel. For information on transformations, see
Using Transformations.
The Input Parameters for <transformation name> window opens.
In this window, the upper section displays the input parameters. The icon next to each parameter represents its type.
4. To display the parameter’s definition, click the Show Definition button.
The window displays the parameter’s name and type.
In the Value group box, all options are mutually exclusive. Null specifies a NULL value; if it is disabled, the parameter cannot be NULL. Literal applies a value selected in the upper section. Query Parameter accepts a name for the parameter.
Each parameter in the upper section is displayed with its initial value. If the parameter is nullable, the default is null. If the parameter is not nullable, the default value depends on type: zero for numeric, an empty string for a character, and the current date for a date.
5. Select the Query Parameter radio button, type the name, and click OK.
Do not include string values in quotation marks. The parameter name must be unique and begin with a letter. It can contain any number of alphanumeric characters and can include an underscore. It must not contain any spaces.
6. Click OK to save the entry and close the window.
The name of the parameter appears as the input parameter (IN) and is also included in the FROM clause.
7. If you want to view the auto-generated SQL script, click the SQL Script tab in Studio.
8. Save the parameterized query.
Here is an example of adding a parameter to the FROM clause of a parameterized query.
The name of the parameter (ticker) appears as the input parameter (IN) and is also included in the FROM clause.
