hi,
Following view-entity will help you because there some aggregate function is
used in side of this entity also there is the use of group by clause.
<view-entity entity-name="
OrderHeaderAndRoleSummary"
package-name="org.ofbiz.order.order"
never-cache="true"
title="Order Header And Roles View Entity">
<member-entity entity-alias="ORLE" entity-name="OrderRole"/>
<member-entity entity-alias="OH" entity-name="OrderHeader"/>
<alias entity-alias="ORLE" name="partyId" group-by="true"/>
<alias entity-alias="ORLE" name="roleTypeId" group-by="true"/>
<alias entity-alias="OH" name="orderId"/>
<alias entity-alias="OH" name="orderTypeId"/>
<alias entity-alias="OH" name="orderDate"/>
<alias entity-alias="OH" name="statusId"/>
<alias entity-alias="OH" name="totalGrandAmount" field="grandTotal"
function="sum"/>
<alias entity-alias="OH" name="totalSubRemainingAmount"
field="remainingSubTotal" function="sum"/>
<alias entity-alias="OH" name="totalOrders" field="orderId"
function="count"/>
<view-link entity-alias="ORLE" rel-entity-alias="OH">
<key-map field-name="orderId"/>
</view-link>
</view-entity>
--
Brajesh
On 24-May-2011, at 3:23 PM, zhiyongcui wrote:
> How to implement sql as below with view-entity definition.
>
> select PRODUCT_ID,FACILITY_ID,LOCATION_SEQ_ID, SUM(case when
> LOCATION_SEQ_ID='A' then QUANTITY_OH_HAND_TOTAL else 0 end) AS QTY1,
> SUM(case when LOCATION_SEQ_ID='b' then QUANTITY_OH_HAND_TOTAL else 0 end) AS
> QTY2 from inventory_item where PRODUCT_ID in (select PRODUCT_ID from Product
> where PRODUCT_TYPE_ID='FINISHED_GOOD') GROUP BY PRODUCT_ID,LOCATION_SEQ_ID
>
> --
> View this message in context:
> http://ofbiz.135035.n4.nabble.com/about-view-entity-tp3546616p3546616.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
