I have a database table, let's call it headers with an id and a String-field called "header". Another table in the database, called subheaders has two fields, headerId and the String field "subheader". There are 0, 1 or 2 subheaders per header. I now want to use Kettle/Pentaho Data Integration to generate an Excel output with the following columns:
header subheader1 subheader2
I tried the following: Table Input on "headers", table input on "subheaders" for subheader1, table input on "subheaders" for subheader2. A "database lookup" is not possible as that doesn't allow me to exclude the subheader1 in the query for subheader2.
My main problem is that I cannot reference the stream fields in the SQL statement directly but instead have to rely on the order of the fields. The first question mark gets filled with the first stream field, and so on.
The queries: For the "headers" table:
SELECT id, header FROM headers
For the "subheaders" table (2 table input steps in a row):
SELECT subheader AS subheader1, ? AS header FROM subheaders WHERE headerId = ?
SELECT ? AS subheader1, subheader AS subheader2, ? AS header WHERE headerId = ? <- doesn't work as I don't have a reference to the header-ID anymore
Any idea, how to solve this problem nicely?
Thanks for any ideas.