Hi @all,
The problem with my namedQuery is that I got several 'validation' errors.
But I can run the code (as a named Query as well as a separated test) and get the results as expected.
For me it looks like an validation error inside the dali/eclipselink/eclipse.
What I want?
Do find out with your help if,
- (and the easiestone would be) it's simply my fault.
- It's a bug
What did I found out?
- It occurs in compination when the aggregatefunction (SUM or COUNT) is in a Select Statement
What envirometal do I have?
- Eclipse Neon.1a Release (4.6.1) && Build id: 20161007-1200
- [EclipseLink] org.eclipse.persistence.jpa Version:2.6.4
What did I NOT check?
- Check with hibernate or other equivalent frameworks
- Other Aggregatfunctions
The code wich caused that trouble is long and you would need to fully go into it, therefore I encapsulated my problem to an simply reproduce-able but nonsense query. The full query is on the very end of this post.
Query-Code (SIMPLYFIED):
-----------------------------------------------------------------------
...
@Entity
@NamedQueries({
@NamedQuery(name = "Product.test", query ="SELECT p.id, SUM(1+1) FROM Product p GROUP BY p.id")
})
...
-----------------------------------------------------------------------
Error Tooltip:
###############################################
Multiple markers at this line
- The encapsulated expression is not a valid expression.
- The right parenthesis is missing from the SUM
expression.
- The query contains a malformed ending.
- The state field path 'p.id' cannot be resolved to a valid
type.
- The identification variable 'p' is not defined in the
FROM clause.
- A select statement must have a FROM clause.
###############################################
Entity:
----------------------------------------------------------------------
...
public class Product implements Serializable, EWMSEntity {
private static final long serialVersionUID = 1L;
protected Product() {
}
public Product(long id, String name, String manufacturer) {
this.id = id;
this.name = name;
this.manufacturer = manufacturer;
}
@Id
private Long id;
private String manufacturer;
private String name;
...
-----------------------------------------------
Query-Code (ORIGINAL):
-----------------------------------------------------------------------
...
@Entity
@NamedQueries({
@NamedQuery(name = "Product.getStock", query ="SELECT p.id, p.name, "
+ "SUM(CASE WHEN m.movementType = 'in' THEN 1 ELSE -1 END * b.quantity) as stock "
+ "FROM Product p "
+ "INNER JOIN p.stockUnits s "
+ "INNER JOIN s.allocations a "
+ "INNER JOIN a.bookings b "
+ "INNER JOIN b.movement m "
+ "GROUP BY p.id")
})
...
-----------------------------------------------------------------------