DB Query using variables on DB

KNIME Analytics Platform
1

Hi there,
I would like to define and use a variable within my sql command to download some aggregated data from a database. For example I create variable row_num which is used within the “select” statement.

set @row_num=0;
select `Runtime [h]`, Group1, avg(Measure) as avgMeas
FROM (
 select
 floor((@row_num:=@row_num+1)/3600) AS `Runtime [h]`, Group1, M1 as Measure
from
 MyTable
 where M1 between 1 and 1000
 ) as T
 group by 1,2

It seems, that KNIME add’s automatically a “SELECT ( * ) FROM (…)” in the background before the command is send to the server. Therefore the definition of the variable “row_num” does not work. Is there a node where I can send unchanged queries to a database?

2

Have you also tried DB SQL Excecuter Node?
br

1 Like
3

Hi Daniel,
that did the trick!

MySQL-Connector → DB SQL Executer Node (set @row_num = 0) → DB Query Reader (“select …”)

1 Like
4

Hi @ActionAndi
Glad that you fixed it. Thanks for sharing your feedback and validate the solution. Certainly helpful for others as well.
br

5

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.