Rule Engine / Conditions on database level

KNIME Analytics Platform
1

Dear KNIMErs,

still trying to find my way through working with databases without having to learn SQL (I am lazy as hell :wink: )

Here’s my question:

I have a table that holds two columns, let’s call them ID_1 and ID_2, see screenshot below
image

I have a set of rules that define, what type of (database) line item this is:

  • IF ID_1 IS NOT MISSING and ID_2 IS MISSING → then it’s a DELETE
  • IF ID_1 IS MISSING AND ID_2 IS NOT MISSING → then it’s an INSERT
  • IF ID_1 IS NOT MISSING AND ID_2 IS NOT MISSING → then it’s an UPDATE

I can easily define this locally in KNIME using a Rule Engine node (see screenshot below)

image

This (local) Rule Engine shows the mode appending a new column (which probably is not possible on database level).

What I want to do instead is

  • if the type is DELETE use ID_1
  • if the type is INSERT use ID_2
  • if the type is UPDATE use ID_2

Is such a “set of rules” possible on database level as well? The reason I am asking is that I need to run this “query” daily and I somehow want to avoid to download 20 million lines just to throw 99.9% away.

Can you help me stay lazy? :smile:

Thanks a lot in advance.

2

Hello @kowisoft,

if you are reading data into KNIME use DB Row Filter node to read only those rows you need.

Every logic applied on data in Rule Engine can be done with SQL. You can do it with DB Query node and CASE statement. See example here:

Br,
Ivan

3 Likes
3

Hi,

does this help you?

image
image1091×529 88.8 KB

I’m not sure what you want to do with the resulting table.

Here’s the link to the Workflow in my KNIME Hub space:
https://hub.knime.com/s/D1GHiTS_da_O_yHv

I think if you familiar with KNIME you are already on the half way to program SQL. Most stuff like “group by”, “join” and others are very similar.