+1
Would this resolves also https://issues.apache.org/jira/browse/OFBIZ-1944 ?
Jacques
From: "Scott Gray" <[EMAIL PROTECTED]>
+1
Regards
Scott
2008/9/18 Jacopo Cappellato <[EMAIL PROTECTED]>:
Hi,
I'm moving here part of my reply to a message in the user list with an idea
to improve the code that prepares select fields for view entities.
What do you think?
Jacopo
Begin forwarded message:
From: Jacopo Cappellato <[EMAIL PROTECTED]>
Date: September 18, 2008 10:00:29 AM GMT+02:00
To: [EMAIL PROTECTED]
Subject: Re: Bestseller : OrderReportSalesGroupByProduct
Reply-To: [EMAIL PROTECTED]
On Sep 18, 2008, at 9:25 AM, Eric DE MAULDE wrote:
Hello,
I would like to introduce into my web site the best sales by product
I try to use (in a groovy file) :
delegator.findByAnd("OrderReportSalesGroupByProduct", [productStoreId :
productStoreId])
I have the following error :
Error rendering screen
[component://mobilier/widget/CatalogScreens.xml#categorydetail]:
org.ofbiz.base.util.GeneralException: Error running Groovy script at
location
[component://mobilier/webapp/mobilier/WEB-INF/actions/catalog/CategoryDetail.groovy]
(SQL Exception while executing the following:SELECT OH.PRODUCT_STORE_ID,
PS.STORE_NAME, OI.PRODUCT_ID, PR.INTERNAL_NAME, OH.ORDER_DATE, OH.STATUS_ID,
OI.STATUS_ID, OH.ORDER_TYPE_ID, RL.PARTY_ID, RL.ROLE_TYPE_ID,
SUM(OI.QUANTITY), SUM(OI.UNIT_PRICE) FROM (((public.ORDER_HEADER OH INNER
JOIN public.ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID) INNER JOIN
public.ORDER_ROLE RL ON OH.ORDER_ID = RL.ORDER_ID) INNER JOIN public.PRODUCT
PR ON OI.PRODUCT_ID = PR.PRODUCT_ID) INNER JOIN public.PRODUCT_STORE PS ON
OH.PRODUCT_STORE_ID = PS.PRODUCT_STORE_ID WHERE (OH.PRODUCT_STORE_ID = ?)
GROUP BY OH.PRODUCT_STORE_ID, PS.STORE_NAME, OI.PRODUCT_ID, PR.INTERNAL_NAME
(ERROR: column "oh.order_date" must appear in the GROUP BY clause or be used
in an aggregate function))
Hello,
the OrderReportSalesGroupByProduct view-entity contains aggregate fields
(e.g. SUM(OI.QUANTITY) ) and simple fields and they cannot appear as
selected fields together, unless you add the simple fields to the group by
section.
This is the meaning of the error: column "oh.order_date" must appear in
the GROUP BY clause or be used in an aggregate function
[...]
PS: to avoid this error we may consider the following modification to the
code that handles view entities:
* if a view entity contains aggregated fields and a "select * " query is
run, then the list of selected fields is composed in this way:
a) all the aggregated fields are added
b) all the fields in the group by section are added
This will also resolve the error is happening in the Webtools generic view
screen when you try to research values from a view entity with agrregated
and non aggregated fields.
Jacopo
