DB Query inconsistent DB data type

Hello,

I am facing this issue with the DB Query Node.

When I execute the following simple query:

SELECT "product", "quantity"*2 as quantity_m
FROM #table# 

the data type is correct on tab preview:

but it is NULL on DB Spec:

Why? Am I missing something?
How can I use the result of the query on the next node?

On the other hand, the following query returns the correct data type on both tab preview and DB Spec:

SELECT "product", "quantity" as quantity_m
FROM #table# 

Thank you!

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.

BR

2 Likes

@zz_knime welcome to the KNIME forum

two things. In the DB node you should be able to set a type mapping. Then there is the option to use a CAST statement to force the desired format like

CAST(my_var AS DOUBLE) AS my_new_var_double
CAST(my_var AS BIGINT) AS my_new_var_bigint

This is also useful to do if sometimes your data sources would contain unwanted elements and your DB would then reject your input data.

For example you could set types in the DB Table Creator node:

Or the SQLite connector:

Hi @mlauber71 thank you very much for your reply.

Both casting and setting types didn’t work :no_mouth:.
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?

Many thanks for your help!

Hi @morpheus
I had the same hypothesis.

It means that unfortunately, it is literally impossible to create a transformation flow using DB nodes. It sounds strange, isn’t it?

I have also found the same outcome on this workflow :

Hi @zz_knime,

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.

BR

1 Like

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

3 Likes

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