Shuchi,
One way is to define a view entity, in your component's entity-model.xml
using <view-entity> tag. You can use
<alias><complex-alias></complex-alias></alias> child tags for your summing
requirement. This view will also be accessible in webtools. In your code
you can then use delegator methods to retrieve the list from this view
entity.
--
Thanks & Regards
Mridul Pathak
Hotwax Media
http://www.hotwaxmedia.com
mridul.pat...@hotwaxmedia.com
-------------------------------------------------
direct: +91 - 942.592.6892
On Tue, Oct 6, 2009 at 7:14 PM, su2 <shu...@pexsupply.com> wrote:
>
> Hi Chris,
>
> Thank you for the response.
>
> My query has multiple joins with different tables and also I would like to
> use sum(column name) and (column 1 + column 2) as column 3.
>
> Is it possible to use sum(column name) in java method with delegator?
>
> Also, the stored procedure I want to use in jasper report.
>
> Thank you for the help.
> Su-
>
>
> Chris Snow-3 wrote:
> >
> > Hi Su,
> >
> > I use SQL Views for complex queries:
> >
> > <entity entity-name="ClearanceReport"
> > table-name="vClearanceReport"
> > package-name="uk.co.dhales"
> > title="Clearance Report Entity"
> > no-auto-stamp="true"
> > never-cache="true"
> > >
> > <field name="strIsrCompany" col-name="strIsrCompany"
> > type="long-varchar"/>
> > <field name="strIsrBranch" col-name="strIsrBranch"
> > type="long-varchar"/>
> > <field name="strIsrCode" col-name="strIsrCode"
> > type="long-varchar"/>
> > <field name="ingUplNumber" col-name="ingUplNumber"
> > type="numeric"/>
> > ...
> > <prim-key field="ingUplNumber"/>
> > </entity>
> >
> > The table name vClearanceReport is actually a SQL View. The attributes
> > no-auto-stamp and never-cache where needed.
> >
> > Also, here is a service that is using a SQL Stored procedure:
> >
> > public class DHSequenceService {
> >
> > public static Map getNextSequenceValue(DispatchContext ctx, Map
> > context) {
> > Connection conn = null;
> > CallableStatement statement = null;
> >
> > String sql = "{call getNextSequenceValue(?,?)}";
> >
> > Long seqValue;
> > try{
> > conn = ConnectionFactory.getConnection("dhmssql");
> > if (conn == null) {
> > throw new Exception("No dhmssql connection
> > configured");
> > }
> > statement = conn.prepareCall (sql);
> > String seqTable = (String)context.get("seqTable");
> >
> > statement.registerOutParameter(1, Types.INTEGER);
> > statement.setString(2, seqTable);
> > statement.execute();
> > seqValue = new Long(statement.getLong(1));
> > statement.close();
> > } catch (Exception e){
> > Debug.logError(e, "Error running SQL - ",
> > DHSequenceService.class.getName());
> > return ServiceUtil.returnError("Error running SQL" + e);
> > }
> > finally {
> > if (statement != null)
> > try {
> > statement.close();
> > } catch (Exception e) {}
> > if (conn != null)
> > try {
> > conn.close();
> > } catch (Exception e) {}
> > }
> > Map resultMap = ServiceUtil.returnSuccess();
> > resultMap.put("seqValue", seqValue);
> > return resultMap;
> > }
> > }
> >
> > Note that by using SQL Views/Stored Procedures you are limiting the
> > portability of your application to other databases.
> >
> > Cheers,
> >
> > Chris
> >
> > su2 wrote:
> >> Hello Friends,
> >>
> >> I need to write quite complex query which is little easier to write as
> >> Stored Procedures. So I would like to know whether is it possible to
> >> write
> >> and access stored procedures in OFBiz with mySQL?
> >>
> >> If its possible, can i have example or reference for how to do that ?
> >>
> >> Thank you for the help in advance.
> >>
> >> Su-
> >>
> >
> >
> >
>
> --
> View this message in context:
> http://www.nabble.com/is-Stroed-Procedures-possible-in-OFBiz--tp25768161p25768992.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>
>
