[eclipselink-users] [NamedQuery/JPQL/Validation] - No Aggregatfunction p

[Stefan Jodl <stefan.jodl@xxxxxxxxxxxxxxxxxx>]

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):
-----------------------------------------------------------------------
Code: 
...
@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:
----------------------------------------------------------------------
Code: 
...
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):
-----------------------------------------------------------------------
Code:
...
@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") 
})
...

-----------------------------------------------------------------------



--
View this message in context: http://eclipse.1072660.n5.nabble.com/NamedQuery-JPQL-Validation-No-Aggregatfunction-possible-within-a-SELECT-tp186048.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.