Pentaho - Reuse field Multiple Times in a query

Question

I have a field, item_id, being passed from one transform step to another. I want to use that field multiple times in the next transform step for use in a UNION.

select x, y from table1 where item_id = ? UNION select w, z from table2 where item_id = ?

Since transforms do placeholder replacement in order by field, how do I make the item_id available to be used multiple times?

Answer 1

One way is to make the two queries in a separate Input tables and direct the flow on the same step (next step can be any step):

An other way is to use parameters instead of variable:

select x, y from table1 where item_id = "${myParam}" UNION select w, z from table2 where item_id = "${myParam}".

An third way is the rewrite the query as

select * from (select x, y from table1 UNION select w, z from table2) where item_id = ?

Answer 2

I used Select task to duplicate the values. Keeps it simple.