Icon

KNIME Table SQL example

There has been no title set for this workflow's metadata.

Example of using the KNIME Table Session Connector component to enable the execution of SQL directly against the data in the table.
The component uses the embedded H2 database to perform this, but this is effectively invisible to the user, who doesn't need to know about database creation, and inserting to the database tables.

The demo provides an alternative solution to the Query, in the form of KNIME nodes to emulate the various parts of the SQL select statement


@takbb Brian Bates 5 June 2024

NON-EQUI JOIN FROM cte c1JOIN cte c2 ON c1.user_name = c2.user_name AND c1.Date&lt;= c2.Date GROUP BY c1.user_name, c1.Date, c1.amount, c1.Total CASEWHEN MAX(c1.amount)-SUM(c2.total) &gt;=0 THENc1.TotalWHEN MAX(c1.amount)-SUM(c2.total)&lt;0 AND c1.Total +(MAX(c1.amount)-SUM(c2.total)) &gt;=0 THEN c1. Total +(MAX(c1.amount))-SUM(c2.total))ELSE 0END AS refund SELECTc1.user_name, c1.Date, c1. amount,c1.Total, &lt;&lt;refund&gt;&gt; ORDER BY c1.user_name, c1.DateDESC @takbb componentSample Datareplace this with anydata source@takbb componentInitial Connector if noprevious sessionsqljoin on user = user(c2)keep only whereuser = user(c2) and date <= date(c2)perform the refund calculationSum(Total) and Max(Amount)by user and date, total and amountas per the group by clauserepeat amountbecause we want max(amount)but also groupingby amountNode 17specify columns (and their sequence)to be returnedby select clause DelimitedText Reader KNIME TableSession Connector DB Query Reader Joiner Rule-basedRow Filter Math Formula GroupBy Math Formula Sorter Table Manipulator NON-EQUI JOIN FROM cte c1JOIN cte c2 ON c1.user_name = c2.user_name AND c1.Date&lt;= c2.Date GROUP BY c1.user_name, c1.Date, c1.amount, c1.Total CASEWHEN MAX(c1.amount)-SUM(c2.total) &gt;=0 THENc1.TotalWHEN MAX(c1.amount)-SUM(c2.total)&lt;0 AND c1.Total +(MAX(c1.amount)-SUM(c2.total)) &gt;=0 THEN c1. Total +(MAX(c1.amount))-SUM(c2.total))ELSE 0END AS refund SELECTc1.user_name, c1.Date, c1. amount,c1.Total, &lt;&lt;refund&gt;&gt; ORDER BY c1.user_name, c1.DateDESC @takbb componentSample Datareplace this with anydata source@takbb componentInitial Connector if noprevious sessionsqljoin on user = user(c2)keep only whereuser = user(c2) and date <= date(c2)perform the refund calculationSum(Total) and Max(Amount)by user and date, total and amountas per the group by clauserepeat amountbecause we want max(amount)but also groupingby amountNode 17specify columns (and their sequence)to be returnedby select clauseDelimitedText Reader KNIME TableSession Connector DB Query Reader Joiner Rule-basedRow Filter Math Formula GroupBy Math Formula Sorter Table Manipulator

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: BATEB031
Created at: 2024-06-04
On NodePit since: 2024-06-05
Last update: 2024-11-21
Created with KNIME version: v5.1.2

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!