Looking for SQL-like operations: EXCEPT, EXISTS etc

[mlauber71]

@dandl

EXCEPT, EXISTS, NOT EXISTS

For exept in data you could use the

Reference Row Filter

For exist there could be the table validator to see if a column is there or use this method:

Add column if doesn't exist

As you need to check if the column exists, you must aply a workaround. Extract the column header and transpose it into flow variables (Extract Column Header-> Table Row to Flow Variable) then use a Java Edit Variable to check the column names. Than generate with the JEV a FlowVariable saying both or bottom. This you can feed into a If Switch node. And generate with the Constant Value Column the column only for the top part. Don't forget to collect both branches from the if node with a End If no…

Also KNIME has a lot of switches to guide your workflow. And also nodes to list external files.

But if you like SQL you could still work with it in KNIME it supports all kinds of databases and with H2 and SQLite you have your own one right there without big installations to do.

Database Reader Skip if Error

OK I built something because also I wanted to experiment with databases and switches. I use SQLite because it provides a simple DB. You might switch that out. 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. It uses Vernalis nodes and also an old entry from Iris. [image] [image] …