About EasyMorph Tutorials & Examples Web-help

Query editor : "In" clause with parameter


#1

Hi,

It is possible to have an equal filter with parameter but impossible to have the same with the “IN” clause. Then “IN” clause requires only constants. What could be simple is to have a parameter syntax like (A,B,C) and in the query, the IN clause generate IN (A,B,C). More complex but suitable : deal with check list parameters instead of simple input textbox. Then you could have an IN clause with parameter.

Of course it is still possible to have a list outside the query editor and to join with to make filter but when you have a big table and you absolutely want to filter on SQL values to parameter before importing, in the current version you have to import the whole table first and then join to the list which is a problem.

It’s also possible to type SQL code “IN {parameter}” with the parameter but it’s not comfortable for users who don’t know about SQL : they won’t be able to do it alone or to change it after.


#2

How would IN ({A},{B},{C}) be different from x={A} OR x={B} OR x={C} ? What’s the advantage?


#3

What if you have one hundrer values in the list :sweat_smile: ?
Of course if you have 2 or 3 values you could create 2 or 3 parameters. But with 100 …


#4

Just to make sure I understand you – are you implying that all 100 values should be provided by a single parameter?

Also check out the “Select matching database rows” action in EasyMorph. It allows filtering records against a list of values right in the database. It requires write access permissions as it creates a temporary table.


#5

There can be many ways to do it. You could have one parameter that contains every values like parameter = {A,B,C …} and you just enable to have a filter “IN {parameter}”. Or you could deal with parameters as list of values instead of simple text field, it would be cleaner. A parameter can be a string yes but also a list of string, why not :slight_smile: And other solutions i don’t know. The goal is to filter by list of values defined by the user, whatever the way to do it.