I’m using a database table selector node and would like to use the following query in it
declare @timestamp datetime;
set @timestamp = ‘$${STimestamp}$$’;
select *
from XXX.dbo.IDs where timestamp = @timestamp
This fails with error message
ERROR Database Table Selector 0:201:0:209 Execute failed: Error while validating SQL query: Must declare the scalar variable “@timestamp”.
The documentation says it is possible to use set etc statements before the select if the statements are completed with a “;”. However, I am getting a problem with the declaration. I’ve tried this with various combinations of having or not having the “;”, but with no success.
I’m using version 3.4.1.
Is this something that’s allowed to do, or am I getting the syntax wrong?
the statements work in sql from a SQL Server Management Studio, so this is KNIME processing the different lines of statements
I think the “;” to separate the statements is creating them into separate queries so that the declaration of the variable is not carried through to the rest of the statements. The error is about the variable not being declared when the variable is used.
I’ve tried this with other statements, it seems very temperamental about what’s acceptable and what’s not. The documentation does not state what is done to the separate statements, but just to put a semi-colon between them.
Is it possible to see what the query is that’s passed to the server before it is executed?
I’ve managed to do what I need by creating #temp tables and inserting into that. It seems there is something about the scope of the declare and set statements and the scope of the variable that makes it not work.
