I wanted to run a single sql query but with multiple variable value depending on my user input (to a parameter).
For Example :
When I type in VEGETABLE in my user parameter I want to run “SELECT * FROM <vegetable_name>”, where I have a bunch of different values for my <vegetable_name> variable, additionally I want to join all the data procured from running the query for all vegetable_names.
How are the vegetable names stored? I think you can get your vegetable names into FME using the User Parameter (e.g. using the WHERE clause in a FeatureReader), and then build your query dynamically (e.g. using a StringConcatenator).
You can then use the dynamically built query in e.g. a SQLCreator or SQLExecutor.
Hope this helps!
EDIT: You can let the database do all the heavy lifting by performing the join on the database itself. This way you only need 1 query (“SELECT * FROM carrots c JOIN potato p ON p.common_column = c.common_column”)