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.
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.
Open the Data grid step: Parameters (1 row).
Open the Table input step - Steel Wheels Products 1.
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.
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.
Open the Data grid step: Parameters (several rows).
Open the Table input step - Steel Wheels Products 2.
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.
It's also possible to receive the parameter values in several rows. Instead of a row, you had one parameter by row.
Open the Data grid step: Parameters (several rows).
Open the Table input step - Steel Wheels Products 3.
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.
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.
