I have a ValueCollectionDatasource defined from the screen as follows that works smoothly.
select e.productionStage, e.operation, e.resource, e.plant, e.quantity, e.planOrActual, e.material, e.capacityOrProduction, e.capacityHours, EXTRACT(YEAR FROM e.prodDate) from erp$ProductionPlanContinuousProdPivot e
now I need to extract MONTH as well as use the date fields in my pivotTable.
When I use ALIAS EXTRACT(MONTH FROM e.prodDate) AS month, EXTRACT(YEAR FROM e.prodDate) as year it doesn’t work. Seems like not supported. Thanks for any help.
Hmm. Both extract year and month work for me. Perhaps it is the alias. Is it really required? I usually don’t use them but rather extract from the result list using the order of the select.
Query query = entityManager.createQuery("select e.productionStage, e.operation, e.resource, e.plant, e.quantity, e.planOrActual, e.material, e.capacityOrProduction, e.capacityHours, EXTRACT(YEAR FROM e.prodDate), EXTRACT(MONTH FROM e.prodDate) from erp$ProductionPlanContinuousProdPivot e");
List results = query.getResultList();
for(List result : results) {
year = result.get(9);
month = result.get(10);
}
You may also consider:
Query query = entityManager.createQuery("select e from erp$ProductionPlanContinuousProdPivot e");
List pivots = query.getResultList();
for(ProductionPlanContinuousProdPivot pivot : pivots) {
year = pivot.getProdDate()
Calendar cal = Calendar.getInstance();
cal.setTime(pivot.getProdDate());
year = cal.get(Calendar.YEAR);
month = cal.get(Calendar.MONTH);
}
Thanks. But as I am using this query in pivot Table, I don’t have that choice to loop! and as you know we have to define the properties with a name in pivot table where property name remains prodDate even Extracted to Year unless I use ALIAS!
You can also create a SQL view and then create a persistent entity, which is based on it. This way, you can define whatever you want. It is most likely also more performant compared to value datasources…
well - for the JPA layer it cannot see any difference between a SQL View and a SQL table. Therefore, it will just transparently work as a regular table. When it only comes to read operations, this is a safe bet.
So, what you can do is to create a view in SQL that defines some kind of aggregation or uses some database specific functions (like date operations and so on). But for the JPA layer it can just be seen as regular columns from a table.
This way, for a lot of cases, when you have problems with the ORM layer and it is only about reading → create a SQL view and map a regular JPA entity (persistent) on it. This way, all the complexity from the JPA layer goes away and is pushed into the datastore.
Studio even supports model generation for views, which means that you can basically just define the view, and generate a JPA model from it and be done. The UI can just use the entity as any other one…