Hi @zz_knime,
i’m not sure that my assumption is correct.
My explanation for this is that quantity*2 is calculated during execution, but the eqauation has no predefined data type and therefore the DB Type is Null and by default defined Knime Type String . Whereas quantity itself is predefined in the database.
Both casting and setting types didn’t work .
Moreover, I have noticed that the same behavior appears with the DB GROUP BY node.
Do you have any other suggestions or an actual workflow that works so I can have a look at it?
I don’t see it as such critical because this behavior is not unexpected. More critical would be, if Knime does not return the correct data type. In your case you expect an integer and knime returns an integer as data type for further processing.
I’m not database expert enough to answer this behavior in that detail. For me it looks not be possible to detect a DBdata type for a sql equation without scanning the whole table. Which for my understanding would slowing down the node dramatically.
For me it looks fine and knime does what it should.
Hello zz_knime,
the problem is that SQLite does not return a proper data type in this case. So you have to manually set the type KNIME Analytics Platform should use via its Type Mapping functionality.
If you are not forced to use SQLite I would would suggest to use the H2 Connector node instead. H2 is also a file based database which does not require any installation but provides proper type handling.
Bye
Tobias