Writing SQL WHERE Clause using Multiple Choice values in FME?

Question

I published a parameter which is of Choice Multiple type. I wish to use values from this parameter for WHERE Clause when Reading Inputed Data in the begin of Workbench.

So there is no problem if I have type of published parameter just Choice, then I use following:

WHERE Clause Tile = '$(Name)' 

but I do not know how to write SQL WHERE Clause statement if I have Multiple Choice and want to read all data for Name of Tiles selected in Multiple Choice, because SQL form above does not work ...

Example:

In multiple choice i select the names: Anna, John, Tim. Then, I want that data for tiles that have those names are read.

Answer 1

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:

1. Assign a variable to the value of the multiple choice parameter using the FME_MacroValues dictionary.
2. Create a list from the variable using str.split() with a space being the delimiter (double-check, not sure on this).
3. 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.
4. 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.

Answer 2

The solution is to split the list into a table in SQL. This is more complex if some of your values contain spaces themselves. I found the trick is to split the table use " as delimiter first. This pulls out the values with spaces in them which you do not want to split further.

By adding a space to the start of the list, any row now starting with a space can be futher split, this time using space the delimiter.

Here is a PostgreSQL specific solution, but it would be to too difficult to port to SQL Server (could use STRING_SPLIT for SQL Server 2016+):

SELECT myField
FROM myTable
WHERE myField IN (
    SELECT CASE WHEN LEFT(s, 1) = ' ' THEN s2 ELSE s END AS value
    -- note the space added to the start of the list
    FROM UNNEST(string_to_array(' $(myMultiChoiseParameter)', '"')) AS s
    LEFT JOIN LATERAL (
        SELECT s2
        FROM unnest(string_to_array(s, ' ')) AS s2
        -- only split rows starting with a space
        WHERE LEFT(s, 1) = ' '
    ) AS lat
        ON True
    WHERE CASE WHEN LEFT(s, 1) = ' ' THEN s2 ELSE s END != ''
)

Answer 3

FME 2012 Beta (nightly build) has introduced:

===========================================================================
=========================== BUILD 12130  20110818 ===========================
===========================================================================
SQLExecutor and SQLCreator: Implemented support for multiple SQL
statements through the FME_SQL_DELIMITER. Implemented ability to ignore
SQL errors by prefixing statements with a
hyphen (C40056 C40314 C42660 PR#29343)

ftp://ftp.safe.com/fme/beta/whatsnew.txt

Beta Builds available ftp://ftp.safe.com/fme/beta/

(Not tested it yet but will be doing so in the near future)