Icon

kn_​forum_​sqlite_​skip_​missing_​column

Skip DB table if a column is not there

Skip DB table if a column is not there

What it does it lists all the databases table1 - table4. Three tables have the column “sales” one does not. The Loop would extract all structures from every table and see if a column “sales” is there. If yes the DB would be loaded, if not the database is skipped.

Check if a column is present on a SQL database and skip table if nothttps://forum.knime.com/t/database-reader-skip-if-error/13203/3?u=mlauber71uses:https://www.knime.com/book/vernalis-nodes-for-knime-trusted-extensionhttps://forum.knime.com/t/add-column-if-doesnt-exist/8059/3 with legacy nodes // create a new string variableInteger input_value = 0;input_value = $counter$;// capture a possible problem with null values - if not the code might crash// https://www.dummies.com/article/technology/programming-web-design/java/and-operators-and-in-java-172137if(input_value == null) { return 0;}else if (input_value >= 1) {return 1;}else {return 0;} create db.sqlitevar_port_choiceselect currentdbprepare thefour DBone withoutthe "sales" columnlist all databasesread structureof DB=> if column"sales" is therekeep the recordcounterread DBmake the decisionabout the presenceof the columntake 0 dataif column not presentresult.xlsxknime://knime.workflow/data/database.sqliteselect currentdbcheck the structureof the database=> if column"sales" is therekeep the recordcountervar_port_choicedecidewhich port to actiavteget the resultignoreempty tablesWHERE 1=2force empty table/data/result.xlsx SQLite Connector(deprecated) Java Snippet(simple) Database TableSelector (deprecated) create data tables Database ListTables (Legacy) Table Row To VariableLoop Start (deprecated) Database To EmptyTable (Legacy) Transpose Rule-basedRow Filter GroupBy ConstantValue Column Database Connection TableReader (deprecated) Java Edit Variable Table Row to Variable(deprecated) IF Switch End IF Loop End(deprecated) Rule-basedRow Filter Database IF Switch(deprecated) Database Sampling(deprecated) Database End IF(deprecated) Excel Sheet Appender(XLS) (deprecated) SQLite Connector DB List Tables Table Row ToVariable Loop Start DB Table Selector DB To Empty Table Rule-basedRow Filter GroupBy ConstantValue Column Transpose Table Rowto Variable CASE Switch Start CASE Switch End Component DB Reader Loop End DB Query Excel Writer Check if a column is present on a SQL database and skip table if nothttps://forum.knime.com/t/database-reader-skip-if-error/13203/3?u=mlauber71uses:https://www.knime.com/book/vernalis-nodes-for-knime-trusted-extensionhttps://forum.knime.com/t/add-column-if-doesnt-exist/8059/3 with legacy nodes // create a new string variableInteger input_value = 0;input_value = $counter$;// capture a possible problem with null values - if not the code might crash// https://www.dummies.com/article/technology/programming-web-design/java/and-operators-and-in-java-172137if(input_value == null) { return 0;}else if (input_value >= 1) {return 1;}else {return 0;} create db.sqlitevar_port_choiceselect currentdbprepare thefour DBone withoutthe "sales" columnlist all databasesread structureof DB=> if column"sales" is therekeep the recordcounterread DBmake the decisionabout the presenceof the columntake 0 dataif column not presentresult.xlsxknime://knime.workflow/data/database.sqliteselect currentdbcheck the structureof the database=> if column"sales" is therekeep the recordcountervar_port_choicedecidewhich port to actiavteget the resultignoreempty tablesWHERE 1=2force empty table/data/result.xlsxSQLite Connector(deprecated) Java Snippet(simple) Database TableSelector (deprecated) create data tables Database ListTables (Legacy) Table Row To VariableLoop Start (deprecated) Database To EmptyTable (Legacy) Transpose Rule-basedRow Filter GroupBy ConstantValue Column Database Connection TableReader (deprecated) Java Edit Variable Table Row to Variable(deprecated) IF Switch End IF Loop End(deprecated) Rule-basedRow Filter Database IF Switch(deprecated) Database Sampling(deprecated) Database End IF(deprecated) Excel Sheet Appender(XLS) (deprecated) SQLite Connector DB List Tables Table Row ToVariable Loop Start DB Table Selector DB To Empty Table Rule-basedRow Filter GroupBy ConstantValue Column Transpose Table Rowto Variable CASE Switch Start CASE Switch End Component DB Reader Loop End DB Query Excel Writer

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: m_lauber
Created at: 2019-01-18
On NodePit since: 2020-02-16
Last update: 2024-04-18
Created with KNIME version: v4.7.1
Tags: sqliteskiptablecaseswitch

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

Try NodePit Runner!