Hi,
Thanks Jacopo.
I've created entity screens (xml).
Bestsellers from OrderReportSalesGroupByProduct and new products (new
articles) from ProductAndCategoryMember entities work well.
I need to leak out these results when a surfer is into a sub-category.
But, in a entity sreen, I choice as operator "in", it doesn't work. I don't
have any result.
I would want to have a productCategoryId list
What class is this parameter ? related to this operator "in"
thanks
Eric
----- Original Message -----
From: "Jacopo Cappellato" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Thursday, September 18, 2008 10:00 AM
Subject: Re: Bestseller : OrderReportSalesGroupByProduct
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
You can do two things:
1) explicitly specify the list of fields to retrieve (passing a list of
field names to the delegator method), and include in it only aggregate
fields (if you don't pass the list, all the fields are selected, and you
get this error)
2) add the simple fields you want to select to the group by section
(again passing a list with field names to the delegator method)
Jacopo
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
I read
http://www.nabble.com/how-to-see-data-in-view-table--td17765415.html#a17765415
How can I order by quantity ?
Do you have a solution ?
Thanks
Eric
