I have two Integer
columns in the database (derby and db2). I need to divide them with each other inside a JPQL
.
Both columns being of type Integer
return zero if remainder is a decimal number e.g 0.25
becomes 0
etc and understandably so since type is int.
In SQL
I could have this for example
select CAST(column1 as decimal(6,2))/CAST(column2 as decimal(6,2))from Sometable;
but what is JPQL
equivalent .
One option might be (I have not tried yet) is to have a @Transient
method in the entity returning the Decimal type and doing this calculation there and pass that to JPQL
but I would rather let SQL
do this work.
Mysql does not require casting at database level . So Behaviour for different RDBMS is different which is fine . But what should JPQL do with out needing to use a native query to know that cast to decimal is needed for this operation.
Adding dialect <property name="openjpa.jdbc.DBDictionary" value="derby"/>
did not fix it either.
Please note it is JPA1
You have basically three options.