DB Row Filter: filter with a flow variable

I would like to control the varibale “bukrs” by an integer variable. The list of integers comes from the “Table row to variable loop start” node. Does someone know how to do it? I tried to click on “flow variables” and to chose the flow variable, but it says: coding issue, DB type.

is the column bukrs an integer datatype in your database?
br

yes it is D - double

I don’t have a DB at hand to test this myself, but it looks like Daniel is refering to the possibility of a type mismatch. Can you try to use a flow variable of type double and see if that works?

If so, you might want to check your results, as doubles are usually not comparable the same way integers are. This has to do with the way continuous numbers are represented with discrete bits. Some information is always lost, and two doubles that look the same, might actually be a tiny bit different at the 10th digit after the coma. Maybe the DB handles that, but if not, you’ll need to compare your values against a small range.

thank you for your input. I have double values everywhere. I am just super confised in the "Flow Variables" option of the DB row filter - look there are like 10 possibilities to choose smth and to change something… what should I select?

My final goal: instead of number 888 - i need a flow variable. The flow variable will come from the “Table row to variable loop start” node. But how to connect it properly inside the Flow Variables? What exactly shall i choose? See i show with red circles

Hmm, yes, the flow variables tab doesn’t have the most descriptive names. It’s because this interface was never meant for us users. Glad we have it though, it’s one of the most powerful parts of KNIME.

The buttons in the flow variables and their respective counterpart in the normal settings tab is ordered in roughly the same way. Stuff that is near the top in one tab will be near the top in the other one. Also, the tree-like structure helps.

I’m 100% sure the right button is either part of the columnSpec branch, or the operation branch. ColumnSpec sounds more like managing the column in the DB though. That leaves us with 3 options. “Values” looks promising, mainly because there’s got to be something left to remote control the comparison operator. It also allows arrays, which makes sense for a filter value. If my guess is wrong, you can also brute-force from bottom upwards.

If my telediagnosis didn’t work, I can try it myself when I get home. I have a DB, just need to find the credentials.

2 Likes

Thank you very much! I would highly appreciate that!

Yes I was referring to the column type. If your database is double then I would also try to convert the Knime variable to double first before you use it in values in the flow variable tab.
br

I connected to my DB (MariaDB if that’s important), did a simple query with a DB Row Filter and everything worked.

Using “equals” on a column of type double doesn’t work, as expected. It has to be a range for it to work (2 conditions: 1x greater than, 1x smaller than).
Filtering a range with Flow Variables works regardless of the type (int, double, string).

condition_X → operation → values controls the comparison value, just as I predicted.

@Milovanova does that work for you?

2 Likes

Thank you for your reply. I will make a screenshot… it does not recognize my value as a flow variable… it says value is empty. And additionally I have also attached my mini-workflow: I create table with numbers, then i use table row to variable (which selects the first row in the bigining and takes the value), and then it is not working…my flwo variable is callled BUKRS (with large letters)


Pic 3

That error message is actually about the empty box in the conditions tab, as all fields that are Flow Variable controlled don’t like empty values. You have to enter something. Doesn’t really matter what, it will be overwritten anyway.
I hope that also fixes the second error message about the casting.

3 Likes

Yes it worked now :slight_smile: Thank you very much!!!

1 Like

This is because of precision. Double (decimals) are not always precised and get rounded off at some point. That is why the range works.

1 Like

I love the detailed explanation to this problem, @Thyme and @Daniel_Weikert!

It now makes sense why the flow variable tabs are so confusing…they weren’t designed for public consumption, but I’m glad we have them!

1 Like

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