Parameters

Passing parameters ..

If you need to create a dataset with data coming from a database, you can do it just by using a Table Input step.

If the SELECT statement that retrieves the data doesn't need parameters, you simply write it in the Table Input setting window and proceed.

However, most of the times you need flexible queries—queries that receive parameters.

This demonstration will show you how to pass parameters to a SELECT statement in PDI.

Assume that you need to list all products in Steel Wheels for a given product line and scale.

database queries - Parameters

Parameters (1 row)

As we're passing the parameters in a single row, we have to careful and ensure the datastream fields are mapped in the correct order according to the WHERE clause.

1. Open the Data grid step: Parameters (1 row).

Data grid - Parameters (1 row)

2. Open the Table input step - Steel Wheels Products 1.

Table input - parameters ? - single row

• The replacement of the markers respects the order of the incoming fields.

• Any values that are used in this manner are consumed by the Table Input step. Finally, it's important to note that question marks can only be used to parameterize value expressions just as you did in the recipe.

• Keywords or identifiers (for example; table names) cannot be parameterized with the question marks method.

Preview data - Parameters (1 row)

Parameters (several rows)

Suppose that you not only want to list the Classic Cars in 1:10 scale, but also the Motorcycles in 1:10 and 1:12 scales. You don't have to run the transformation three times in order to do this. You can have a dataset with three rows, one for each set of parameters.

1. Open the Data grid step: Parameters (several rows).

Data grid - Parameters (serveral rows)

2. Open the Table input step - Steel Wheels Products 2.

Table input - parameters ? - several rows

• The replacement of the markers respects the order of the incoming fields.

• Any values that are used in this manner are consumed by the Table Input step. Finally, it's important to note that question marks can only be used to parameterize value expressions just as you did in the recipe.

• Keywords or identifiers (for example; table names) cannot be parameterized with the question marks method.

Preview data - Parameters (serveral rows)

Parameters (1 by row)

It's also possible to receive the parameter values in several rows. Instead of a row, you had one parameter by row.

1. Open the Data grid step: Parameters (several rows).

Data grid - Parameters (1 by row)

2. Open the Table input step - Steel Wheels Products 3.

Table input - parameters ? - 1 by row

• The replacement of the markers respects the order of the incoming fields.

• Any values that are used in this manner are consumed by the Table Input step. Finally, it's important to note that question marks can only be used to parameterize value expressions just as you did in the recipe.

• Keywords or identifiers (for example; table names) cannot be parameterized with the question marks method.

Preview data - Parameters (1 by row)

Note that this approach is less flexible than the Parameters (1 row). For example, if you have to provide values for parameters with different data types, you will not be able to put them in the same column and different rows.