I extracted and transformed worksite records from a Oracle dB and stored them in a PostGIS dB. There are about 700 worksite records having a unique ID.

I need to extract additional worksites from the Oracle dB based on some spatial queries, which I only want to execute in close proximity of the 700 initially selected sites.

For this I want to use SQL creators transformers and in the WHERE clause of the query I would like to be able to use something as :

WHERE ID in $(id_list)

$(id_list) would be the list of my 700 initial records (78748, 48575, 05843, 47585, etc.)

I tried doing this by creating a list of all my ids but this doesn't seem to work. list-executor

Do I have to do this using a multiple choice parameter as suggested at Writing SQL WHERE Clause using Multiple Choice values in FME?

I can't seem to open the documentation to fully understand this solution.


Just need to add a list concatenator using ',' as a separator.

FME Workflow

| improve this answer | |

Well a "list" (in terms of the ListBuilder) is an FME construct that isn't really used outside of FME, which would include SQL.

As @Lay mentioned, you could use the ListConcatenator to join the values of that list together into a comma-separated attribute that you could use.

The other alternative would be to avoid lists altogether and just go with an Aggregator transformer, using the Attributes to Concatenate parameter.

Having said that, what exactly are you reading through the Reader in your workspace? Does each feature that's being read provide a different ID to query?

In that case I would just point those features into the SQLExecutor. That would produce the same results as a single feature with an "in" command, and I think it might even be faster. An "in" command with so many values can be wildly inefficient I believe.

Alternatively you might even try a DatabaseJoiner transformer. It depends on what your SQL actually does, but if it's just a standard Select statement, then a join might be faster and more efficient, with pretty much the same results. If it's a spatial join then try the FeatureReader transformer instead.

In short: there's lots of different ways, with varying performance. If we knew more about the structure of the features being read, it would help to select which is best.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.