You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ofbiz.apache.org by Jacopo Cappellato <ja...@hotwaxmedia.com> on 2008/09/18 11:03:14 UTC
Improving the code that prepares select fields for view entities
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 <ja...@hotwaxmedia.com>
> Date: September 18, 2008 10:00:29 AM GMT+02:00
> To: user@ofbiz.apache.org
> Subject: Re: Bestseller : OrderReportSalesGroupByProduct
> Reply-To: user@ofbiz.apache.org
>
>
> 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
>
Re: Improving the code that prepares select fields for view entities
Posted by Jacopo Cappellato <ja...@hotwaxmedia.com>.
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" <le...@gmail.com>
>> +1
>> Regards
>> Scott
>> 2008/9/18 Jacopo Cappellato <ja...@hotwaxmedia.com>:
>>> 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 <ja...@hotwaxmedia.com>
>>>> Date: September 18, 2008 10:00:29 AM GMT+02:00
>>>> To: user@ofbiz.apache.org
>>>> Subject: Re: Bestseller : OrderReportSalesGroupByProduct
>>>> Reply-To: user@ofbiz.apache.org
>>>>
>>>>
>>>> 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
>>>>
>>>
>>>
>>
Re: Improving the code that prepares select fields for view entities
Posted by Jacques Le Roux <ja...@les7arts.com>.
+1
Would this resolves also https://issues.apache.org/jira/browse/OFBIZ-1944 ?
Jacques
From: "Scott Gray" <le...@gmail.com>
> +1
>
> Regards
> Scott
>
> 2008/9/18 Jacopo Cappellato <ja...@hotwaxmedia.com>:
>> 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 <ja...@hotwaxmedia.com>
>>> Date: September 18, 2008 10:00:29 AM GMT+02:00
>>> To: user@ofbiz.apache.org
>>> Subject: Re: Bestseller : OrderReportSalesGroupByProduct
>>> Reply-To: user@ofbiz.apache.org
>>>
>>>
>>> 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
>>>
>>
>>
>
Re: Improving the code that prepares select fields for view entities
Posted by Scott Gray <le...@gmail.com>.
+1
Regards
Scott
2008/9/18 Jacopo Cappellato <ja...@hotwaxmedia.com>:
> 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 <ja...@hotwaxmedia.com>
>> Date: September 18, 2008 10:00:29 AM GMT+02:00
>> To: user@ofbiz.apache.org
>> Subject: Re: Bestseller : OrderReportSalesGroupByProduct
>> Reply-To: user@ofbiz.apache.org
>>
>>
>> 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
>>
>
>