Skip to main content
Solved

Limit subsequent reads based on initial result set?

  • February 17, 2015
  • 1 reply
  • 6 views
P
Forum|alt.badge.img
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.

Best answer by takashi

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
View original
Did this help you find an answer to your question?
<strong>This post is closed to further activity.</strong><br /> It may be a question with a best answer, an implemented idea, or just a post needing no comment.<br /> If you have a follow-up or related question, please <a href="https://community.safe.com/topic/new">post a new question or idea</a>.<br /> If there is a genuine update to be made, please contact us and request that the post is reopened.

1 reply

takashi
Evangelist
  • takashiEvangelist
  • Best Answer
  • February 18, 2015
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

Helpful Members This Week

Community Stats

31,408
Posts
119,108
Replies
39,030
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.