Icon

kn_​example_​switch_​case_​store_​file_​sql

An example of combining SQL database, loop and case switch

An example of combining SQL database, loop and case switch
there are 10 chunks in the database. some of them have fault=1 lines some do not. The chunks with faulty lines should be handled differently and get a separate export to Excel
https://forum.knime.com/t/how-can-i-avoid-having-to-open-and-close-a-database-connection-twice-when-working-with-a-sub-workflow-that-is-only-called-when-a-certain-condition-is-met/61127/10?u=mlauber71

OUTER LOOP - 10 iterations one for every chunk (https://forum.knime.com/t/how-can-i-avoid-having-to-open-and-close-a-database-connection-twice-when-working-with-a-sub-workflow-that-is-only-called-when-a-certain-condition-is-met/61127/10?u=mlauber71) CASE SWITCH An example of combining SQL database, loop and case switchthere are 10 chunks in the database. some of them have fault=1 lines some do not. The chunks with faulty lines should be handled differently and get a separate export to Excelhttps://forum.knime.com/t/how-can-i-avoid-having-to-open-and-close-a-database-connection-twice-when-working-with-a-sub-workflow-that-is-only-called-when-a-certain-condition-is-met/61127/10?u=mlauber71 SELECT COUNT(*) as number_of_faultsFROM "my_table"WHERE 1=1AND "chunk" = $${Ichunk}$$AND "fault" = 1 knime://knime.workflow/data/my_database.sqliteyour local SQL databasemy_tablecreate a list of all chunksin the databaseSTART10 iterationsfor the 10 chunksselect all lines with faultsWHERE 1=1AND "chunk" = $${Ichunk}$$AND "fault" = 1number_of_faultsif number_of_faultsnot 0 then export the lines0 top branch1 bottom branchactivebranchreturn $${Inumber_of_faults}$$.equals(0) ? "0" : "1";chunk_<>.xlsxsheet:"number_faulty_lines"var_excel_pathvar_excel_namechunk_<>.xlsxsheet:"faulty_lines"ENDknime://knime.workflow/data/my_database.sqlite=> just run once to create the SQLite databaseSQLite Connector DB Table Selector DB GroupBy DB Reader Table Row ToVariable Loop Start DB Query Reader DB Query Reader Table Rowto Variable CASE Switch Start Java EditVariable (simple) Excel Writer Create File/FolderVariables Java EditVariable (simple) CASE Switch End Excel Writer Variable Loop End create theinitial database OUTER LOOP - 10 iterations one for every chunk (https://forum.knime.com/t/how-can-i-avoid-having-to-open-and-close-a-database-connection-twice-when-working-with-a-sub-workflow-that-is-only-called-when-a-certain-condition-is-met/61127/10?u=mlauber71) CASE SWITCH An example of combining SQL database, loop and case switchthere are 10 chunks in the database. some of them have fault=1 lines some do not. The chunks with faulty lines should be handled differently and get a separate export to Excelhttps://forum.knime.com/t/how-can-i-avoid-having-to-open-and-close-a-database-connection-twice-when-working-with-a-sub-workflow-that-is-only-called-when-a-certain-condition-is-met/61127/10?u=mlauber71 SELECT COUNT(*) as number_of_faultsFROM "my_table"WHERE 1=1AND "chunk" = $${Ichunk}$$AND "fault" = 1 knime://knime.workflow/data/my_database.sqliteyour local SQL databasemy_tablecreate a list of all chunksin the databaseSTART10 iterationsfor the 10 chunksselect all lines with faultsWHERE 1=1AND "chunk" = $${Ichunk}$$AND "fault" = 1number_of_faultsif number_of_faultsnot 0 then export the lines0 top branch1 bottom branchactivebranchreturn $${Inumber_of_faults}$$.equals(0) ? "0" : "1";chunk_<>.xlsxsheet:"number_faulty_lines"var_excel_pathvar_excel_namechunk_<>.xlsxsheet:"faulty_lines"ENDknime://knime.workflow/data/my_database.sqlite=> just run once to create the SQLite databaseSQLite Connector DB Table Selector DB GroupBy DB Reader Table Row ToVariable Loop Start DB Query Reader DB Query Reader Table Rowto Variable CASE Switch Start Java EditVariable (simple) Excel Writer Create File/FolderVariables Java EditVariable (simple) CASE Switch End Excel Writer Variable Loop End create theinitial database

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: A9699459
Created at: 2023-02-10
On NodePit since: 2023-02-10
Last update: 2024-04-26
Created with KNIME version: v4.7.1
Tags: caseswitchloopsqlsqlite

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

Try NodePit Runner!