database sorter

Node Development
1

I am new to Knime, and trying to evaluate its potential to our dev team.

The first thing I notice when tracing the MS SQL calls it is making is how it forms the SQL into a subquery.

I have opened a table, then tried to sort the contents using the database sorter. The query it generates is as follows:
SELECT * FROM (SELECT * FROM (SELECT * FROM addressPoint) table_2073446251 ORDER BY street_address, city, province, postcode) table_728169394

This type of subquery is causing an error for the database sorter. Can anyone provide some insights on why queries are formed in this way and how to get by this most basic of tasks.

Thanks.

2

I think each database node wraps the incoming query into a new subquery, hence this sort of behaviour. If this is causing an error, then entering the SQL directly into a Database Reader node, or even Database Table selector node may help

Steve

3

I can get database reader to work with a sorter after it. I am unable to get the database sorter to work because of the way it structures the sub-queries. I would like to figure out how the database sort works. I am assuming it functions and that I am not using it correctly.

Thx, Marc.

4

Hi Marc,
what database are you connecting to? As Steve already mentioned KNIME uses sub queries to generate the SQL statements so every db node adds another sub query to the incoming query. If the database does not support sub queries you will need to write your own sql statement and execute it with the Database Reader node.
Bye
Tobias

5

Usually sub-query does not allow ORDER BY. So you could add it only to the last select.