Retrieve results from a batch of SQL queries in Pentaho or Postgres?

Question

I'm still relatively new to SQL and Pentaho.

I've pulled a table with two different IDs and need to run a query for each specific instance.

For example,

    SELECT * 
    FROM Table 
    WHERE RecordA = 'value in column A' 
    AND RecordB = 'value in column B'

I need the results back, either appended to new columns in the original table or part of their own text file output.

I was initially looking at using a formula for this inside of Pentaho, but couldn't quite figure it out. Since I have the query written I threw it into Excel and got the concatenated results (so a string of 350 or so queries that I need to run). I'm just not sure how to accomplish this - I tried the Execute SQL Script inside of Pentaho but it doesn't seem to do output?

Any direction would be useful. I've searched a little but have come up short so far, possibly because I am still pretty new to this platform.

Answer 1

You can accomplish this behavior in a lot of ways, with a "Database Lookup" step for example, but I usually do that in a quite easy way and here is a example for your tests, I hope it helps.

The idea here is to have two Table input steps, the first one will fetch the IDs we want to look at. For example you may use a SQL query similar to note on the left. The result will be a 1 column stream of rows.

Next we have a Table Input that reads the rows received and executes it's query for each row. I'll add a screenshot with the options that I selected.

What it does is replace a placeholder '?' with the data that is received. If you need two columns use two '?' but remember that it will replace the first one with the first column and the second one with the second column

And you are good to go. Test it a couple of times and good luck.

And the config for the second table input.