I've recently been evaluating KNIME and I'm running into a weird behavior.
Issue
If I use a a Column List variable twice inside the query, the execution throws an error with The value is not set for the parameter X (where X is the second use of the Column Variable).
Error
ERROR Parameterized Database Query 0:74 Execute failed: com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 2.
SQL
SELECT *
FROM #table# as dc
WHERE FirstName = $FirstName$
OR LastName = $FirstName$
If I remove the second $FirstName$ the query works fine. I've been scanning docs and the internet but haven't found exactly anything regarding the issue. I understand what the errors means, but inside KNIME I'm guessing it can't handle using the same variable twice.
I've built some fairly complex and successful flows with KNIME now but this one issue is slowing me down to the point I have to start thinking of another tool.
Thank you to those who have responded asking for more information... I've produced a very basic flow to demonstration the issue.
Flow
Read Data from Excel Spread Sheet
Name is combined into one string which get splits apart into FirstName and LastName.
Append USA State Designation based on ZIP
Using unique results for each row we want to find matching records into a Microsoft Database table
Debug Flow
Passing Query
SELECT * FROM #table# as dc
WHERE NPI = $NPI$
AND LastName = $LastName$
Failing Query
SELECT * FROM #table# as dc
WHERE NPI = $NPI$
AND LastName = $LastName$
OR FirstName = $LastName$
ERROR Parameterized Database Query 0:113 Execute failed: com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 3.
I've since wrote some workaround to get things moving along but interesting that this issue is happening at all.