Writing SQL WHERE Clause using Multiple Choice values in FME?
If you have FME 2011 you can use scripted parameters to read in the multiple values into an "IN" statement for your where clause. Scripted parameters can use either Tcl or Python -- Tcl is built-in to FME while Python is not, so if it needs to be as portable as possible (such as on a server where Python is not available) Tcl is probably the way to go.
Take a look at the templates mentioned in the above link (looks like you need FME Workbench to download and view them). Also check pages 15-18 of Server Authoring 5 - Advanced Workflows.
I am unable to write code for you since I don't currently have access to FME to test it, and the best way to learn is to do it yourself, but here is a description of what I would probably do in Python:
- Assign a variable to the value of the multiple choice parameter using the
FME_MacroValues
dictionary. - Create a list from the variable using
str.split()
with a space being the delimiter (double-check, not sure on this). - In a list comprehension, add value delimiters
(usually single quotes, but this is DBMS-specific) inside of a
str.join()
to make the comma-delimited list string required by an IN statement. - Return the string.
Then in your parameter for the WHERE clause change the equality statement to an IN statement, and reference the scripted parameter within the IN statement.
Finally there may indeed be a SQL-only way to do this, but it is most likely going to be DBMS-specific and likely involve dynamic SQL or stored procedures which are probably way overkill for this. Since this is essentially a string operation it's probably best to do it in a script rather than in the DBMS.