Re: Improving the code that prepares select fields for view entities

[Jacopo Cappellato]

On Sep 18, 2008, at 8:15 PM, Jacques Le Roux wrote:

+1

Would this resolves also https://issues.apache.org/jira/browse/OFBIZ-1944 ?

Yes, It should resolve that issue too.

Jacopo

Jacques

From: "Scott Gray" <[EMAIL PROTECTED]>

+1
Regards
Scott
2008/9/18 Jacopo Cappellato <[EMAIL PROTECTED]>:

Hi,


to improve the code that prepares select fields for view entities.

What do you think?

Jacopo


Begin forwarded message:

I'm moving here part of my reply to a message in the user list with an idea

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 try to use (in a groovy file) :


productStoreId])

I have the following error :

Error rendering screen
[component://mobilier/widget/CatalogScreens.xml#categorydetail]:

location

OI.STATUS_ID, OH.ORDER_TYPE_ID, RL.PARTY_ID, RL.ROLE_TYPE_ID,

JOIN public.ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID) INNER JOIN

in an aggregate function))

I would like to introduce into my web site the best sales by product delegator.findByAnd("OrderReportSalesGroupByProduct", [productStoreId : org.ofbiz.base.util.GeneralException: Error running Groovy script at [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, SUM(OI.QUANTITY), SUM(OI.UNIT_PRICE) FROM (((public.ORDER_HEADER OH INNER 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

Hello,


section.

the GROUP BY clause or be used in an aggregate function

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 This is the meaning of the error: column "oh.order_date" must appear in

[...]

code that handles view entities:


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


and non aggregated fields.

Jacopo

PS: to avoid this error we may consider the following modification to the * if a view entity contains aggregated fields and a "select * " query is 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

smime.p7s
Description: S/MIME cryptographic signature