Use comma separated variable to produce a SQL query string

[atti_effe]

Hello,

i have a situation in which an user should instantiate a variable done like that:
Locations => 1 , 2 , 5 , 9
Doing some manipulation i have to use this information to make a column with a sql query like that:

select staticfield, anotherstaticfield, hellofield, “dynamicfields” from table where “dynamiccondition”

where
dynamicfields is: Location1, Location2, Location5, Location 9
dynamiccondition is: (Location1 <>0 or Location2<>0 OR location5<>0 or Location9 <>0)

I did this:
1- created a column by the variable, removing the “spaces” using “Column Expressions” block
2-splitted the cell by the “comma” separator, so i obtain something like Location[0] =>1 Location[1]=>2, Location[2] => 5, Location[3] => 9
3- initialized empty column like dynamicselect=“” and dynamicwhere=“”
4- tried with a Column List Loop Start / Loop End (Column Append)
5- used inside the loop a Column Expression that should “replace” dynamicselect and dynamicwhere like that: dynamicselect= dynamicselect + “, Location”+column(“currentColumnValue”), and something similar about the WHERE condition

THE PROBLEM:
this column expression (but i tried also with math formula) doesn’t append on the original column “dynamicselect” the new values, but for each column it add a “dynamicselect (Item N)” element with just that value and i tried many many methods to avoid that without success.

Expectation:
Step 1: dynamicselect passing from “” to “, Location1”
Step 2: dynamicselect passing from “, Location1” to “, Location1, Location 2”
Step 3: dynamicselect passing from “, Location1, Location 2” to “, Location1, Location 2, Location 3”

Results:
Step 1: dynamicselect stayed “”, found a created column dynamicselect (Item 0) with value “, Location1”
Step 2: dynamicselect stayed “”, found a created column dynamicselect (Item 1) with value “, Location2”
and so on… at the end i have dynamicselect, dynamicselect (Item 0), dynamicselect (Item 1), dynamicselect (Item 2)

Of course i selected the “replace column” checkbox instead of create new column

Have you some advice for me? Maybe a totally different solution, or maybe some explanation on how to do it with the loop?

Thank you

[bruno29a]

Hi @atti_effe and welcome to the Knime Community.

The results that you are having is probably because of this:
4- tried with a Column List Loop Start / Loop End (Column Append)

While you are still using the “replace column” option, it’s replacing within that iteration. Once the operation is done, it will append the result at the end of the loop since you chose the Column Append for Loop End.

Why do you need to use the Loop End (Column Append) instead of just a Loop End? Try with Loop End, and if this does not work, it might be better to share your workflow.

I’m not sure why you need to separate the input into individual column though. I can see this being done with some String Manipulation without needing to create individual columns, and therefore without needing a Loop altogether.

But I’m not seeing your use case to understand how all this is supposed to come together. I mean, where is that select statement coming from? Is it just 1 statement, or do you have a table of statements?

Here’s an example where all of this can be done without a Loop:

For the sake of the demo, I created these 2 variables:

“Locations” is basically the input from the user
“Query_Template” is just a template of the query with ##dynamicfields## and ##dynamiccondition## as placeholders

Here’s my expression for creating the dynamic fields:

join("Location", replace(replace($${SLocations}$$, " ", ""), ",", ", Location"))

My expression for creating the dynamic condition:

join("(", replace($${Sdynamicfields}$$, ", ", " <>0 or "), ")")

At this point, this is how the dynamicfields and dynamiccondition look like:

The final manipulation is to replace the placeholders with the new variables:

replace(replace($${SQuery_Template}$$
    , "##dynamicfields##", $${Sdynamicfields}$$)
    , "##dynamiccondition##", $${Sdynamiccondition}$$)

And here’s the result:

You can then use this Query variable in your Query Reader node.

[takbb]

Hi @atti_effe , I think I could see what you were trying to do, but hopefully @bruno29a has nailed it as usual.

One thing I might add though is that when you have your dynamic list of columns, and dynamic where clause as variables, you don’t necessarily need to then use the String Manipulation for further replacements in the sql query. If you have a static “template” query, you can use the variables directly in a DB Query reader node and it will replace the variables for you with the strings that they contain:

Both methods should work, and comes down to personal preference and also how how much of your SQL (if any) is always fixed.

[atti_effe]

Hello @bruno29a thank you very much for your support.
Of course you are right that i could do it working on string replaces of course, and for the exact purpouse of my question, this is the solution

Probably in the future i will need for that LOCATION to do something more complicated so i would like to well understand the way of the “loop”, so i could include some advanced feature or customization for specific “location”.
For a studying purpose i would like to find a way using some iteration and about that, for answer to your question, i tried also to use the standard End Loop but the results was having many “rows” when i just need 1 row in which append value on the column.

I upload the workflow sample, thank you

LocationExample.knwf (25.7 KB)

I found inspiration on this approach here: Looping over all columns and manipulation of each

[Daniel_Weikert]

Not sure if I fully understood but could you leverage ungroup concatenate and crossjoiner to create the parts of the select statement you need and then follow up with additional string manipulation or sth.?
Sth like that

Sorry it’ s already late if my answer is wide of the mark please ignore it. Just trying to help
br and have a nice evening

[bruno29a]

Hi @atti_effe , thank you for sharing your workflow. I see what you are trying to do there.

Unfortunately, I don’t think this can work like this. I mean, you won’t be able to concatenate like this.

Basically, each iteration starts over with the original values of the columns that are not included in the column list. You can easily test this by adding a dummy value to them in your Table Creator, and you will see them in each of the iteration.

Knime seems to propagate variable value change only, not column value change. The only Loop that I know that would do that is the Recursive Loop. Unfortunately, I don’t see how you can use the Recursive Loop here.

This might not be a proper example to do what you are trying to do. I’ve checked the link you provided where you found the inspiration from, and it’s not exactly the same use case. This example was probably simulating what we have today as Math Formula (Multi Column).

For your case, if you really want to use the Loop (for study), you probably would need to use variables within the Loop. Convert column to variable, and append (join) to variable, and then covert back to column after.

[atti_effe]

Ok… do you think that maybe using the standard End loop, that creates 3 rows for me, after that i could use some block to merge those line concatening that values with my “favourite” separator?

I’d like to have the control about each single “location” because in this way i can introduce some specific feature, for instance for some Location that are > than 7, because of my ERP system, i should find the field in another table, so adding a complexity to my previous example.

So i’m think on:

• try to “merge” those 3 rows created using End Loop
• try with other approach, maybe something that use Loop over Variables?

which one would you try?

thanks

[takbb]

Hi @atti_effe, the merging of the rows is possible using a “Group By” node. Tell it to concatenate the column and use a space as the separator

In terms of the looping, I believe @Bruno29a is right when he says that to do what you are trying to do in a loop, returning only a single output row would require a recursive loop. Other loops will either collect each row, or only the last row and each iteration is “unaware” of the previous iteration.

The recursive loop allows “feedback” on each iteration whereupon the data from the iteration can be passed to the next iteration. However, you then do not have the Column List loop functionality that you need.

It can be done though but adds a lot of additional complexity to the process, so it really depends how much you want to do it.

I decided I wanted to do it… (or die trying ;-))

So here is an example of a recursive loop (and a load of other nodes) into which you can place your original three nodes. The additional nodes are there to create a list of the columns you wish to iterate over, and then for each iteration pass the “next” column. At the end it produces a single row containing the output… I think…

Which means you simply have to decide whether your original (plus Group By)

is easier to maintain than this beast…

LocationExample Recursive Loop.knwf (72.8 KB)

Enjoy!