You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by Eric DE MAULDE <er...@free.fr> on 2008/09/18 09:25:38 UTC
Bestseller : OrderReportSalesGroupByProduct
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))
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
Re: Bestseller : OrderReportSalesGroupByProduct
Posted by Malin Nicolas <ma...@librenberry.net>.
Slt Eric,
If you use group by on a view, your select juste return attribute when a
group by or a fonction is use on
In your select you need only this attribute
OH.PRODUCT_STORE_ID, PS.STORE_NAME, OI.PRODUCT_ID, PR.INTERNAL_NAME
For the quantity you need use a sum fonction on your view .
example :
<alias entity-alias="II" name="quantityOnHandTotal" function="sum"/>
Nicolas
Eric DE MAULDE a écrit :
> 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))
>
> 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
>
--
Nicolas MALIN
Consultant
Tél : 06.17.66.40.06
Site projet : http://www.neogia.org/
-------
Société LibrenBerry
Tél : 02.48.02.56.12
Site : http://www.librenberry.net/
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
>>
>
>
Improving the code that prepares select fields for view entities
Posted by 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: Bestseller : OrderReportSalesGroupByProduct
Posted by Eric DE MAULDE <er...@free.fr>.
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" <ja...@hotwaxmedia.com>
To: <us...@ofbiz.apache.org>
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
>
>
Re: Bestseller : OrderReportSalesGroupByProduct
Posted by Jacopo Cappellato <ja...@hotwaxmedia.com>.
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