I have just figured out how to connect Knime to our SQL servers here and that is very promising as it will remove a lot of generating Excel files to feed into our workflows etc. The only issue I am having now is using variables in my SQL statement. It works fine in Report Builder, but Knime doesn’t like my DECLARE statement at the start. Basically, I need to declare a year and then feed that into my WHERE statement. From September to December, I need it to be the current year. From January to August, I need it to be the previous year. So, I wrote this:
DECLARE @StatEdLeaving as varchar (4)
SET @StatEdLeaving = CASE
WHEN MONTH(GETDATE()) BETWEEN 9 and 12 THEN YEAR(GETDATE()) + 1
WHEN MONTH(GETDATE()) BETWEEN 1 and 8 THEN YEAR(GETDATE())
ELSE ‘’
END
That gives me the four-character year I need to feed into my main statement. However, Knime (and various other programs I use!) do NOT seem to like the DECLARE at the start. I have tried to get my head around several ways of getting DECLARE to work or somehow substituting it with a variable from the workflow, but I have been unable to replicate that so far. Does anyone have any ideas? Thanks!
Below is just pseudocode, but it demonstrates a potential way to avoid having to declare variables for this altogether.
select *
from sample_data
where
(month(sample_data.date) between 9 and 12 and year(sample_data.date) = year(getdate()) + 1)
or (month(sample_data.date) between 1 and 8 and year(sample_data.date) = year(getdate()))
Thanks, I’ll have a look at that. I did have a side flow where I basically generated the year I need and turned it into a variable, which I fed into the query reader (so rather than “@Year” in SQl, I have a Knime “$$Year$$”). That seems to work well. Looking at your response, I assume that means there isn’t really a way of doing this succinctly and it will need some sort of code or variable regardless?
Looking at your response, I assume that means there isn’t really a way of doing this succinctly and it will need some sort of code or variable regardless?
I’m honestly not sure if declaring variables is supported for SQL scripting against your database, but putting logic (similar to what I provided) in the WHERE clause would probably work fine for your needs and would avoid the issue altogether.
Thanks; good to know I’m not just missing something completely obvious as well! IT’s frustrating that it does not work in Knime (and Tableau, too!) as it does in SQL Report Writer, but at least I have a couple of options for a workaround now. Thanks again
you can use local variables in the DB Query Reader node. This node sends the query as is to the database for evaluation. I was able to successfully execute the following query with our SQL Server which returned the value of the local variable as result:
DECLARE @StatEdLeaving as varchar (4)
SET @StatEdLeaving = CASE
WHEN MONTH(GETDATE()) BETWEEN 9 and 12 THEN YEAR(GETDATE()) + 1
WHEN MONTH(GETDATE()) BETWEEN 1 and 8 THEN YEAR(GETDATE())
ELSE ‘’
END
SELECT @StatEdLeaving as result
If you want to combine this with the visual query building you can use the standard nodes to define the query part without the local variable first and then inject it into the DB Query Reader via flow variable by using the DB Query Extractor node as show below.
