DB row Filter with String Widget

Hi All,
I have a table which has over 10 million rows. I want to filter on a column which is categorical and i want the user to select multiple categories right at the start of the query.
I don’t want the DB Reader node first since that takes in a lot of time.
How do i add a list of my choices as a flow variable to my condition in the DB row filter?

For example:-
My DB Table selector has a custom query select a,b,c,d from xyz;
then DB row filter has a condition of b = ‘1234’ set
i am connecting a string input widget and i am passing '1234,‘5678’ to this condition but its not working
technically, it should be b in (‘1234’,‘5678’)

How can i achieve it? Thanks in advance

Hi r_jain, I couldn’t work out if you just had a typo in your example of what you are passing, as there appears to be a quote missing.

I have attached a sample workflow showing ways to the IN list using a flow variable. In one case, where the list is numerics, and in the other where it is strings.

For the user input, I have added a component that you may find useful. It takes a list of items and turns them into an array variable. This means the user doesn’t have to put all the quotes in, so it removes that obstacle. The array variable returned by the component is then turned into either a concatenated comma-separated list if it is numerics, or alternatively into a quoted-comma-separated list, and an additional String Manipulation node then adds on the additional quote at beginning and end.

You don’t need to do all that. You can just have the user type in the list, but making them put the quotes in correctly is painful! :wink:

The flow variable itself is passed to the IN clause by simply putting it between the brackets as follows:

image

For demo, I’ve used a db query reader, but it should work similarly for the DB Table Selector with custom query.

Demo DB IN Clause using Flow Variable.knwf (64.6 KB)

If you want to use the String input component, feel free. It is available on the hub here:

I hope that helps.

1 Like

Hello @r_jain, @takbb,

think DB Looping node preceded with Table Creator node is sufficient in this case.

Br,
Ivan

2 Likes

Hi @ipazin, yet another useful node that I wasn’t aware of :wink:

Is there an in-database equivalent, so that if @r_jain is building using DB Table Selector and DB Row Filter and so on, the IN clause can be included as part of the in-database query?

The only way I can see of including the IN clause in-database rather than as the final reader is to include the IN clause in the DB Table Selector custom query (where I have the DB Query Reader in my above demo) using a flow variable.

1 Like

Hello @takbb,

Don’t think so. Possibly DB Row Filter but that one doesn’t feature IN clause and it’s hard to control with flow variables. Instead of DB Table Selector I would use DB Query node as it’s more intuitive and easier to document flow :wink:

Br,
Ivan

Choices! Choices! :slight_smile:

btw @ipazin. it’s good to see you back as a regular on the forum again!

The only thing I have against the DB Query Node is that I don’t have the option of attaching it directly to a database connector… so I end up just attaching a Table Selector to the db connection and bunging the query in there (with a suitable annotation of course! :wink: )

Think this has now been done sufficiently “IN” depth… :slight_smile:

Demo DB IN Clause using Flow Variable2.knwf (79.4 KB)

3 Likes

This is great. Thanks! I tried the parametrized DB query reader which is sort of similar like DB looping.
In the empty table creator i was pushing the entire string in one row since i want the user to list down their preferences using a string widget.
Is there a way to get multiple options pushed onto the table creator using string widget.? The numbers can have alphabets so i would need string for the same.

Yes you can use the String Widget and generate an input to the DB Looping Node, if that’s what you mean. You don’t use a Table Creator for it as you just need to generate an input table:

image
Demo DB IN Clause using Flow Variable3.knwf (92.7 KB)

In this case, the user doesn’t put in the quotes, they simply have to put in the commas, even though the input is Strings.

2 Likes

Incidentally, the Parameterized DB Query Reader will work too using the same mechanism and exactly the same configuration


You get a slightly different result set too (but the same db data is returned):

Parameterized DB Query Reader
image

vs DB Looping
image

1 Like

Hello @takbb,

Ask for a feature request :wink:

Tnx. Always loved KNIME community but sure needed a rest :slight_smile: And then again now there are many great KNIMErs like you these days so don’t feel that needed :sweat_smile:

Br,
Ivan

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.