+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
>>
>
>
