Hi Chris,
It is a list/result of multiple table joins.
I have requirement of displaying both aggregated (summed) values as well as
to display the detail (unsummed) records.
I have little idea to get detail/unsummed record as list using <form
type=list>. But I do not have any clue how to use sum(column name) with the
java delegator.
I have following simple Query - SELECT with WHERE clause. 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.
Su-
Chris Snow-3 wrote:
>
> Hi Su, how are you using the returned data? Is it a table list, or just
> a single form?
>
> If it is a list, do you want to display just the aggregated (summed)
> values, or do you want to display the detail (unsummed) records too?
>
> su2 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--tp25768161p25769503.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
