Skip to main content
Hi,

 

 

This may be a newbie question/solution but I can't find a good way to implement the proper combination of reader and transformer parameters in order to streamline my workbench definition.  Here is the scenario:

 

 

Multiple readers with a huge amounts of records (all PostGIS tables).

 

 

One reader is the 'Master' table which contains rows with unique IDs.  The other tables have 1:Many rows with foreign keys associated with the unique ID.

 

 

My objective is to not have every record read in each of the "supporting" tables once a result set is obtained from an initial query on the "master" reader/table.

 

 

So, let's say that I set up a WHERE Clause on the master reader/table where it returns 1000 distinct records/IDs.  How do I set up the workspace so that when supporting readers/tables are accessed, only a limited set is saught and returned from each of the supplimentary readers/tables and then joined.

 

 

So, in summary, it is going through the process of creating a result set, then using that result set in a before-transaction query process for each remaining reader.

 

 

Thanks very much in advance.
Hi,

 

 

As far as I know, there is no way to refer to reader's result directly from another reader.

 

If the tables belong to the same database, this WHERE Clause could work to get the required result.

 

e.g.

 

"ID" in (select "ID" from "Master" where <condition>)

 

 

But it may be a little bit inefficient. Alternatively, you can also set SELECT Statement to the reader, rather than WHERE Clause.

 

e.g.

 

select s.* from "Support" as s inner join "Master" as m

 

on s."ID" = m."ID" where <condition>

 

 

In addition, I prefer to use the SQLCreator or the SQLExecutor instead of Readers in such a case. You can set a SQL statement to the transformer to create a result table.

 

I think the SQLCreator/SQLExecutor is more flexible and efficient when you need to create a result table by joining multiple tables.

 

 

Takashi

Reply