how to execute my own sql by OFBiz delegator

[sheng]

Hello everyone,
I have an entity defined as:
<entity entity-name="Commodity">
      <field name="quantity" type="long"/>
      <field name="price" type="long"/>
</entity>

How can l get the following sql execution result by OFBiz delegator?
SELECT (quantity*price)/SUM(quantity) average FROM commodity WHERE price>100;

Thanks a lot


     

[Atul Vani]

hi Sheng,

one way could be executing the raw SQL query, like

stmt = connection.createStatement();
sql = "SELECT ...";
rs = stmt.executeQuery(sql);

but i would rether prefer use of ComplexAlias, you can find an example in
CountFacilityInventoryByProduct.groovy

    qohDiffComplexAlias = new ComplexAlias("-");
    qohDiffComplexAlias.addComplexAliasMember(new
ComplexAliasField("IITE", "quantityOnHandTotal", null, "sum"));
    qohDiffComplexAlias.addComplexAliasMember(new
ComplexAliasField("PRFA", "minimumStock", null, null));
    prodView.addAlias(null, "offsetQOHQtyAvailable", null, null, null,
null, null, qohDiffComplexAlias);
    atpDiffComplexAlias = new ComplexAlias("-");
    atpDiffComplexAlias.addComplexAliasMember(new
ComplexAliasField("IITE", "availableToPromiseTotal", null, "sum"));
    atpDiffComplexAlias.addComplexAliasMember(new
ComplexAliasField("PRFA", "minimumStock", null, null));
    prodView.addAlias(null, "offsetATPQtyAvailable", null, null, null,
null, null, atpDiffComplexAlias);

--
Thanks & Regards
Atul Vani
Enterprise Software Developer
HotWax Media Pvt. Ltd.
http://www.hotwaxmedia.com/
We are the Global Leaders in Apache OFBiz, Google 'ofbiz' and see for yourself.


sheng wrote:

> Hello everyone,
> I have an entity defined as:
> <entity entity-name="Commodity">
>       <field name="quantity" type="long"/>
>       <field name="price" type="long"/>
> </entity>
>
> How can l get the following sql execution result by OFBiz delegator?
> SELECT (quantity*price)/SUM(quantity) average FROM commodity WHERE price>100;
>
> Thanks a lot
>
>
>      
>
>