4

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.

-1

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)

  • 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
4

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.

  • I wrote very simple python code: import 'VELG' velg1 = FME_MacroValues['VELG'] return velg1 and it does not work, i got the message in FME log file: Starting Translation ... INCLUDE -- failed to evaluate Python script `def ParamFunc(): import 'CHOICE' velg1 = FME_MacroValues['CHOICE'] return CHOICE1 value = ParamFunc() macroName = 'SK' if value == None: return { macroName : '' } else: return { macroName : str(value) } ' Program Terminating Translation FAILED. I wrote this just to check if simple thing is working – Z77 Aug 26 '11 at 9:44
  • You should not need any import statements, and that wouldn't be correct import syntax anyways. For Python syntax questions please read the Python tutorial or search StackOverflow. – blah238 Aug 26 '11 at 14:47
  • 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

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.