0

I am trying to write SQL statement in WHERE Clause FME in Navigator but it does not work.

WHERE Clause:

NAME IN SELECT CONCAT(''$(',SELECT REPLACE ('Roman Ann Jack',' ',','),')'')

This sql code suppose to be NAME IN '$(NAME)' but NAME is actually list of names derived from published parameter defined as Multi Choice Value. I think I have to replace EMPTY CHARACTER between names to comma character and that should be recognizable as list for FME SQL?! I asked this question before, and got answers that this suppose to work in FME2012 or by writing python script.

But at the moment I am curious about this sql statement and if it is syntax and logic ok?! Can anyone check if whole or just part

SELECT CONCAT(''$(',SELECT REPLACE ('Roman Ann Jack',' ',','),')'')

is working?

Improve this question
5
  • 1
    Is there some reason this answer to your previous question is unusable?
    – blah238
    Commented Oct 12, 2011 at 18:17
  • What do you expect FME to do with this "SQL"? Perform macro expansions AFTER executing the inner select?
    – Raven
    Commented Oct 12, 2011 at 19:09
  • @blah238 I tried as you said but had so much problems with python scripting that I was tired of trying it anymore (at this moment). Just this. So at this moment I am just trying to figure out what is wrong with SQL statement, why it does not work. I marked what you said and I will try do this after desember because than I am going to attend python training.
    – Z77
    Commented Oct 13, 2011 at 6:57
  • @Raven I expect that SQL will read multiple values from the published parameter NAME and according to this valued read inputed data and proceed them to manipulation in workbench.
    – Z77
    Commented Oct 13, 2011 at 7:00
  • @Z77 Can you provide examples of the WHERE clause you want to provide to FME, and an example of what it should look like after macro expansion? I think most of us are confused because your example SQL does not contain FME macros (e.g. $NAME) yet still has '$' symbols in funny looking places. And could you also identify the underlying database so that we know what SQL operators are valid?
    – Raven
    Commented Oct 13, 2011 at 20:14

2 Answers 2

Reset to default
2

Well, you could theoretically just use the command:

WHERE NAME IN $(NAME)

However, the main problem is - as you say - that the multi-choice delimeter is a space character and not a comma. So that won't work as-is.

I don't know about the CONCAT and REPLACE stuff.

What I would do is create a second parameter of the type "Scripted" called (for example) MySciptedParam. There are types for Tcl or Python.

Within that parameter definition do a regular expression (if Tcl), something like:

regsub -nocase -all " " $(NAME) ","

Though my syntax may be a little off, I'm going from memory.

Then in the SQL you can legitimately use:

WHERE NAME IN $(MyScriptedParam)

If you're determined to fit it all into one SQL statement, then perhaps you could ask on StackOverflow.com, or even http://dba.stackexchange.com? After all, that part isn't a GIS-specific issue, just a SQL one.

Improve this answer
1
  • The other problem is the syntax for string literals is different between most DBMS's and that produced by the Multiple Choice parameter (discussed previously in comments of this answer).
    – blah238
    Commented Oct 14, 2011 at 0:51
0

I guess that Clause word should be removed !

Try : 

WHERE NAME IN SELECT CONCAT(''$(',SELECT REPLACE ('Roman Ann Jack',' ',','),')'')

or :

WHERE NAME IN (SELECT CONCAT(''$(',SELECT REPLACE ('Roman Ann Jack',' ',','),')''))
Improve this answer
6
  • Yes, of course about the Clause. It stays there just by default! It does not work (both statements you wrote).
    – Z77
    Commented Oct 12, 2011 at 8:56
  • any explicit SQL error message is displayed?
    – simo
    Commented Oct 12, 2011 at 9:38
  • Just this in FME Log file (blue colored text): Could not find the features in the table 'Person'. One possible reason is that a WHERE clause was specified that references a column/field that does not exist in the table. The error number from ArcObjects is: '-2147220985'. The error message from ArcObjects is: {ArcGIS product not specified. You must first bind to an ArcGIS version prior to using any ArcGIS components.}
    – Z77
    Commented Oct 12, 2011 at 9:43
  • And have you tried running the same query into arcgis?
    – simo
    Commented Oct 12, 2011 at 12:23
  • 3
    None of this is valid SQL...
    – blah238
    Commented Oct 12, 2011 at 20:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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