Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 0 down vote accepted

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)

share|improve this answer
1  
I think this is already implemented in readers and writers - in FME2011 - the change here is just to add that capability to the SQLExecutor and SQLCreator transformers. –  Mark Ireland Aug 29 '11 at 16:27
1  
I'm also not sure if this will work in this case. A multiple-choice parameter returns "a b c" (where a b and c are the choices). So you would still have to use the scripted parameter to pull it apart; and even then you wouldn't need multi statements because you could write "where x=a OR x=b OR x=c". What would be ideal is if they were comma delimited - because then you could put "where x in $(param)". I may file an enhancement request for this. –  Mark Ireland Aug 29 '11 at 16:31
    
@Mark, you would also have to convert or add value delimiters correctly for the DBMS. Strings usually require single quotes, numerics don't, and then dates are just a whole 'nother can of worms. –  blah238 Aug 29 '11 at 19:54
    
The problem we have had is doing a SQL select statement whist cross referencing from two separate databases. Works in SQL Developer but not via SQLExecutor. –  Mapperz Aug 29 '11 at 20:58
1  
Yes, I filed a request and suggested we add both a delimiter and the option to quote fields. Really there are reasons to do so even aside from the SQL aspect. –  Mark Ireland Aug 31 '11 at 19:11
add comment

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.

share|improve this answer
    
But can you maybe me write an example how to do it in python? Because I am not familiar with it. I think this is strange that such a problem is not possible to be solved in SQL. –  Z77 Aug 26 '11 at 6:44
    
Updated answer with more information. –  blah238 Aug 26 '11 at 8:01
1  
A small correction: Python is included with FME, currently v2.7, and many of our own transformers use it! –  Mark Ireland Aug 27 '11 at 0:33
1  
I've been told it is a full Python installation. –  Mark Ireland Aug 31 '11 at 19:10
1  
@blah238 Depends what you mean by a "full" installation. FME_HOME/fmepython27 contains everything installed by a Python installer, but the FME installer does not run a Python installer. So don't expect Python environment variables to be set or DLLs to be placed in the standard locations. –  Raven Sep 2 '11 at 18:21
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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