Skip to main content
  • EN
Hi all!

 

 

I have a parametrizing question.

 

 

I use a sql for a dynamic sql creator. This uses paramaters so user has some pickin choices.

 

This question concerns the in operator:

 

somecodes in ($setofcodes)

 

where $setofcodes is a multichoice parameter.

 

 

Now, some wise db manager thougth it wise to do

 

Gytpes (l), (v) etc.  (lines, vlak (wich is dutch for area)) etc.

 

I turn this into 'l','v' as  the in function needs this : gtypes in ('l','v')

 

 

When it concerns numbers, somecode in ($setofcode) works.

 

When its stringtype it doesnt do anything.

 

I create setofcodes like this:

 

regexp_replace(regexp_replace('l/t/v v,mv l v 2p','( a-z0-9/,]+)','''\\1''',1,0,'i'),' ',',',1,0,'i') wich generates 'l/t/v','v,mv','l','v','2p' to use  in the in function:

 

in my sql it is regexp_replace(regexp_replace($geotypes,'(la-z0-9/,]+)','''\\1''',1,0,'i'),' ',',',1,0,'i')

 

 

the bit 'l/t/v v,mv l v 2p' is provided by a myultichoice parameter if one would choose all those that is). This mulitchoice parameter adds a space between choices like 'l v' etc.

 

 

I tried concatenation: somecode in '('||$setofcodes||')' Also with triple quotes). No dice.

 

It seems to fail to see the outer braces needed for the in function.

 

No error arises, just an empty solution.

 

 

the script using the parameter works for integers. Because those dont have quoutes.

 

And of course it works if i type in the string 'l/t/v v,mv l v 2p' rather then have it generated trough a multichoice parameter.

 

 

I can do this

 

SELECT 

 

        t.id  id,

 

        t.objectcode objectcode,

 

        rtrim(regexp_replace(c.GEOTYPE ,'.(>0-9a-z,/]+).','\\1',1,0,'i')) geotypes,

 

        t.inwin  inwin,

 

        t.nivo  nivo,

 

        t.geometrie geometrie,

 

        t.guid      guid,

 

        c.CLASSIFICATIE

 

FROM    dgdtw_topografie t,

 

        class_leiden c

 

where t.objectcode = c.objectcode

 

  and t.verval is NULL

 

  and rtrim(regexp_replace(c.GEOTYPE,'.(<0-9a-z,/]+).','\\1',1,0,'i')) in ('l','g')

 

  and (t.OBJECTCODE in ($objectcodes)

 

  or $betweens)

 

  $nivo

 

  order by t.nivo asc

 

 

but if i replace 'l','g' with

 

(regexp_replace(regexp_replace($geotypes,'( a-z0-9/,]+)','''\\1''',1,0,'i'),' ',',',1,0,'i') with same content, it wont work

 

also the script work when i limit the gtype choice to just one type and write it like

 

rtrim(regexp_replace(c.GEOTYPE,'.(<0-9a-z,/]+).','\\1',1,0,'i')) = '$geotypes'

 

 

($nivo is parameter for level and will either be empty or consist of a piece of sql)

 

 

Allt the regexp scripts work in sql creator, no need to test those... ;)

 

 

 

Hi,

 

 

just a thought: it might be easier to perform this processing in a Python scripted parameter. You can then pass the parameter value into the where clause of your SQLCreator.

 

 

Personally, I find this strategy a bit less opaque and easier to debug, as you can develop test cases for you algorithm outside of FME, e.g. using the Python interactive shell or IDLE.

 

 

David
I'll try that out soon.

 

 

I did try that using a scripted tcl parameter to generate the sql statement. But when i tried using user parameters in the scrupted one, i got some: using parameter reference out of something something error.
Hi,

 

 

a couple of potential sources of errors when using scripted parameters like this:

 

  1. The order of the parameters in the Navigator matters. In a scripted parameter, you cannot reference a parameter further down the list, only those above. Drag and drop to reorganize the order.
  2. I've had some strange errors when using scripted parameters directly into the SQLExecutor. What I did to circumvent this was to create a regular text parameter that references the scripted parameter. You can then reference the "static" text parameter in the SQLExecutor SQL statement.
David
Thanks for the tips.

 

 

 

the script does work when i use a single choice parameter.

 

It als works when in case of multichoice, the parameters are integers.

 

 

It's the combination of stringvalues and the sql "in" function.

 

Like:  'A' in ('A','B','C') WHere the latter part is created using a regexp in SQL.\\

 

(regexp somethingsomething)  I also tried '('||(regexp something)||')' etc.

 

It seems to me that the outer brackets are not recognized.

 

The same happens when i do this directly in SQL-creator.

 

 

No error message, i just get no result.

 

 

I 'll try building the string in a scripted parameter, and take care of the order of parametes.

 

Let you know if and when i succeed.

 

 

Thanks.

 

 

BYe,

 

Gio

Reply


Community Stats

30,162
Posts
114,487
Replies
37,809
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.