In order to follow this recipe, you need the Steel Wheels database.

Carry out the following steps:

The Dynamic SQL row step is a very powerful step for looking for data in a database.

If we take, for example, the first row in the dataset, the SQL statement (the one that you build with the Java expression) is similar to the following:

SELECT PRODUCTNAME, PRODUCTSCALE, BUYPRICE FROM PRODUCTS WHERE PRODUCTDESCRIPTION LIKE '%Aston Martin%'

That's exactly the search you wanted to perform for that row: look for the records where the column PRODUCTDESCRIPTION contained Aston Martin.

Now look at the following SQL statement for the last row:

SELECT PRODUCTNAME, PRODUCTSCALE, BUYPRICE FROM PRODUCTS WHERE PRODUCTNAME LIKE '%Corvette%' AND PRODUCTSCALE="1:24"

In this case, you are filtering both by the name and by the scale.

As you see, what you are doing is dynamically creating an SQL statement. Then, in the Dynamic SQL row configuration window, you just use the SQL field name to indicate which field contains the SQL statement to execute.

As the output of the Dynamic SQL row step, you can retrieve any number of columns. Each database column in the SELECT statement will become a new field in your dataset.

In order to tell Kettle the metadata of the new fields being added with this dynamic statement, you must fill in the Template SQL (to retrieve Meta data) frame. Here, you have to type any statement that returns the same structure as the new fields. Kettle will take from here both the names and the types for the new fields.

In the recipe, you typed SELECT 'NAME', 'SCALE', 1 as BUYPRICE. With this statement, you are telling Kettle that you are adding three fields: two strings named NAME and SCALE and an integer field named BUYPRICE.

In the recipe, you checked the Outer join? option. The effect of this is the same as in the Database join step: for the rows where the lookup fails the new fields are retrieved with null values. That was the case for the second row. There were no products with Ford Falcon as part of its name and scale 1:18. Therefore, that row shows empty values for the PRODUCTNAME, PRODUCTSCALE, and BUYPRICE fields.

For the third row, the Corvette product, you can see two results. This means that the Dynamic SQL row found two matching rows in the database and retrieved them both. This also resembles the behavior of the Database join step.

Finally, note that in the recipe, the statement was different for each row. It may happen however, that your statements do not change a lot. If this is the case, you can reduce the number of physical database queries by checking the Query only on parameters change option.

The Dynamic SQL row step is the most flexible step for looking up data in a database. As you saw, there are a couple of similarities between this and the Database join step. What really makes a difference between them is that with the Dynamic SQL row step, any part of the statement can be built dynamically based on the fields in your stream. For example, the columns used for comparison in the recipe were the product description in the first row and the product name in the others. What you did in the recipe is not possible to achieve by using the Database join, at least in a simple fashion.

Note that you could also have had the statement already built, for example, in a property file or saved in a column in a database table. In that case, you also could have used the Dynamic SQL row step to execute the statement and that is definitely impossible to do with any other step.

The recipe named Looking for values in a database (with complex conditions or multiples tables involved) in this chapter. Look at this recipe for understanding how to look in a database by using the Database join step.