Parameter Database Query: The value is not set for the parameter - using column variable twice

KNIME Analytics Platform
#1

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.

Cheers!

#2

One last try before I replace this with another product - any thoughts on why you can't use a column name twice in the query?

#3

Hi bmay68,

 

In order to better understand the problem, could you please provide a sample workflow and also explain what you're trying to achieve? 

Thank you.

 

Best,

Anna

#4

Hi bmay68,

I tried to reproduce the behaviour you described, however in my case i do not get any error.

Please find attached a sample workflow. 

 

Best,

Anna

#5

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.

 

 

#6

I have the same problem. I use math formula for copy a new column and use both of them.