SQL filter by a Temporary combined column

I have a sql database table that I need to filter before Reading. The problem is that it has a column for YEAR and a column for MONTH. I want to filter for a specific Start and End date range. I only have read permissions on the db. Since Knime does not appear to have any manipulation nodes for sql, does anyone have a suggestion on how I could filter the data.

So far my thought was something like:
START=START YEAR100+START MONTH
END=END YEAR
100+END MONTH
Somehow combine the YEAR and MONTH columns from the db and filter >= START and <= END
Problem is, I can not see how to temporarily create a column with the db values to filter against.

Thoughts?

Hi @AndrewDeacon , you are already on the right lines. The attached workflow gives one possible example of how a SQL query in a DB Query Reader can utilise the values from flow variables to “pre-limit” the returned data from an H2 sql database. You will have similar constructs in your database, and should hopefully be able to apply something similar.

db_pre_select.knwf (26.1 KB)

2 Likes

I should add a word of caution though… if you have a very large amount of data (and I’m assuming you do because otherwise you’d just bring it all back), and the table you are selecting from has indexes on year and month, then selecting data in this way, with a where clause constructed as a calculation will disable the use of the index in just about any relational database that I’ve seen.

If that is the case, you may find it more performant to query
with the where clause just saying something like

year between year-from and year-to

select * from my_data where
(year >= $${Isel-year-from}$$ and year <= $${Isel-year-from}$$ ) 

… and then doing the fine-level filtering of the specific months locally when the data has been returned to KNIME. It really depends on your actual data volumes and the database structure/indexes as to which would be more appropriate, but just thought I ought to mention that.

3 Likes

Great suggestion. There is a lot of data and unfortunately I was not the db architect to add a better filtering column. I can definitely pull in the year’s worth, though and do the advanced filtering outside of the db. Thanks for the help.

2 Likes

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.