DataBase Row Filter: Error with "Database Connection Table Reader"

Hi,

I want to filter the rows of a table in a database to exclude rows that have NULL values in a certain column. I use "Is not NULL" in the configuration of the "Row Filter".

I build a Knime Model with nodes connected as follows: "Database Connector" -> "Database Table Selector" -> "Database Row Filter" -> "Database Connection Table Reader". See attachment.

Everything runs green, up to the "Database Connection Table Reader"... this is throwing the error:

"Execute failed: SAP DB Tech JDBC:  [260] (at 110): Invalid Column Name: FLOATVALUE line3 col 56 (at pos 110)".

Note that "FLOATVALUE" is the column I try to filter on, if I use other columns to filter, I am gettng corresponding errors. I noticed that in the "Database Row Filter" node, the column is displayed as "FloatValue", whereas in the error, it is displayed as "FLOATVALUE" - not sure if small/capital letters are the problem...

1) Is the connections (ordering) between my nodes ok?

2) Has somebody faced this problem and knows the reason and has a solution?

 

Best

FloPits

 

Hi,

 

I did investigate a bit further. The SQL-information found in the "Database Row Filter" ->  "Databse connection" -> Tab: "Connection" is as follows:

 

Database Type:  sap

SQL Statement:
SELECT * FROM (SELECT * FROM "DRUCKER_LOCAL_DATA_RAW2" WHERE "FloatValue" IS NOT NULL) table_2023614530 WHERE ValueType IS NOT NULL

Database Type:  sap

SQL Statement:
SELECT * FROM (SELECT * FROM "DRUCKER_LOCAL_DATA_RAW2" WHERE "FloatValue" IS NOT NULL) table_2023614530 WHERE ValueType IS NOT NULL

 

This is automatically generated - I think the problem comes from the not-quoted ValueType in the where-statement, which is translated in to VALUETYPE and this capital-letter-column does not appear in the Table in the database, only "ValueType".

 

It seems similar issue when joining two tables using "Database-Joiner" (the on-statement columns are not quoted).

The question is: is there a way to _neatly_  (I dont want to change all column-names to capital in the database...) avoid this behaviour?

 

Hi,

I also guess that the problem is the not quoted column name. KNIME by default only quotes column names that do not contain only letters and numbers e.g. spaces.

The only workaround I see right now to your problem is to write your own statement which quotes the problematic column names. You can execute this statement either in the Database Table Selector statement or the Database Query node.

Bye,

Tobias