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,'([a-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... ;)
