Hard time creating multiple selection filter on SQL Query Reader

[French_Wrench]

Hello everyone,

I’m new to the KNIME kommunity and the tool itself. I’m trying to understand how it works on the example database “Northwind”.

I created some visualizations and now would like to add some filters but there I hit a blocker. I tried setting up Multiple Selection Widget, added values to “Possible Choices” that correspond to product categories in the database (in this format):
“Beverages”
“Condiments”
“Confections”
“Dairy Products”
“Grains/Cereals”
“Meat/Poultry”
“Produce”
“Seafood”

Then I try to assign the variable to the SQL Query Reader:

SELECT
products
FROM “order_details”
JOIN “orders” ON “orders”.“order_id” = “order_details”.“order_id”
JOIN “products” ON “order_details”.“product_id” = “products”.“product_id”
JOIN categories ON products.category_id = categories.category_id
WHERE categories.category_name IN $${Sproduct_category}$$

But there is an error. I tried several things, changing the variable in SQL statement:
$${Sproduct_category}$$
‘$${Sproduct_category}$$’
“$${Sproduct_category}$$”
($${Sproduct_category}$$)

Finally it didn’t throw an error with this SQL statement:
WHERE categories.category_name IN (‘$${Sproduct_category}$$’)

But I get an empty table as the result.

Any ideas?

Thanks in advance

[mlauber71]

@French_Wrench welcome to the KNIME forum.

Maybe you can first test the IN statement before adding more complexity. You could collect the elements of the statement in a loop or with a group by node and then feed that as one single flow variable to the SQL. You might want to check for your specific database syntax concerning quotation marks.

[French_Wrench]

Hi mlauber71,

Thanks for the answer. You were right with the quotation marks syntax. I changed the items, in Multiple Selection Widget, to use single quotation marks ’ instead of the double ones " and now it works.

I also had to change back the SQL WHERE statement to this:
WHERE categories.category_name IN ($${Sproduct_category}$$)

Thanks again