Hi Mridul,
Thanks for the reply. But <complex-alias> does not work if I want it in a
view-entity which is created using other view-entities(I think somebody
mentioned on my that post earlier that its a bug and he will look into it
and if not resolved he will create JIRA issue). It just works if i need to
create a view-entity using two entities.
If I could use <complex-alias>, then my life would have been much easier.
I have following simple Query - SELECT with WHERE clause in a Java Method.
But I need to have
SUM(OTCMAdjustmentAmount). (Here OrderAndTax is a view-entity created using
two other view-entities.)
--------------------------------------------------------------------------------------
List fieldsToSelect = UtilMisc.toList("OTCMAdjustmentAmount");
EntityConditionList whereConditions = new
EntityConditionList(UtilMisc.toList(
new EntityExpr("OTCMOrderNo", EntityOperator.EQUALS,
OTCMOrderNo),
new EntityExpr("OTCMAdjustmentAmountType",
EntityOperator.EQUALS, "SALES_TAX")
), EntityOperator.AND);
orderTaxObject = delegator.findByCondition("OrderAndTax",
whereConditions, null, fieldsToSelect, null, null);
--------------------------------------------------------------------------------------
I really appreciate your help.
Thank you.
Shuchi-
Mridul Pathak-2 wrote:
>
> 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
> [email protected]
> -------------------------------------------------
> direct: +91 - 942.592.6892
>
>
> On Tue, Oct 6, 2009 at 7:14 PM, su2 <[email protected]> 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.
>>
>>
>
>
--
View this message in context:
http://www.nabble.com/is-Stroed-Procedures-possible-in-OFBiz--tp25768161p25770141.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
