I need to generate the below query, I am able to do everything apart from the TO_CHAR(opCase.writeoff_dt, 'mm') MOnth

Can some to help to write the Predicate for the same

select sum(dtl.ovrpymnt_case_adjstmnt_am) Adj_AM, sum(dtl.op_case_fees_am) Fees_AM , sum(dtl.ovrpymnt_case_adjstmnt_am) + sum(dtl.op_case_fees_am) Total , TO_CHAR(opCase.writeoff_dt, 'mm') MOnth from ufactssys.UI_OP_WRITEOFF hdr inner join ufactssys.UI_OP_WRITEOFF_DTL dtl on hdr.overpayment_writeoff_id =dtl.overpayment_writeoff_id inner join UFACTSSYS.ui_op_case opCase on opcase.overpayment_case_id= dtl.op_case_id where TO_CHAR(opcase.writeoff_dt, 'mm') >=1 and TO_CHAR(opcase.writeoff_dt, 'mm') <=3 group by TO_CHAR(opCase.writeoff_dt, 'mm');

Below the .Net code for the same ?

ResultsetFields fieldsEligible = new ResultsetFields(3); fieldsEligible.DefineField(UiOpWriteoffDtlFields.OvrpymntCaseAdjstmntAm, 0, AggregateFunction.Sum); fieldsEligible.DefineField(UiOpWriteoffDtlFields.OpCaseFeesAm, 1, AggregateFunction.Sum); fieldsEligible.DefineField(UiOpCaseFields.WriteoffDt, 2); //fieldsEligible.DefineField(UiOpCaseFields.WriteoffDt.ToDateTime().Month, 2); //fieldsEligible.DefineField(UiOpCaseFields.WriteoffDt, 3); //ResultsetFields result = new ResultsetFields(1); //result.DefineField(UiOpCaseFields.WriteoffDt,0); //result.

            //fieldsEligible[2].ExpressionToApply.ToDateTime().Month; 
            GroupByCollection groupByEligible = new GroupByCollection();
            groupByEligible.Add(UiOpWriteoffDtlFields.OvrpymntCaseAdjstmntAm);
            groupByEligible.Add(UiOpWriteoffDtlFields.OpCaseFeesAm);
            groupByEligible.Add(UiOpWriteoffDtlFields.OvrpymntCaseAdjstmntAm);

            RelationCollection relEligible = new RelationCollection();

            EntityRelation relUiOpWriteOffWithUiOpWriteOffDtl = new EntityRelation();
            relUiOpWriteOffWithUiOpWriteOffDtl.AddEntityFieldPair(UiOpWriteoffDtlFields.OverpaymentWriteoffId, UiOpWriteoffFields.OverpaymentWriteoffId);
            relUiOpWriteOffWithUiOpWriteOffDtl.HintForJoins = JoinHint.Inner;
            relEligible.Add(relUiOpWriteOffWithUiOpWriteOffDtl);

            EntityRelation relUiOpWriteOffWithUiOpCase = new EntityRelation();
            relUiOpWriteOffWithUiOpCase.AddEntityFieldPair(UiOpCaseFields.OverpaymentCaseId, UiOpWriteoffDtlFields.OpCaseId);
            relUiOpWriteOffWithUiOpCase.HintForJoins = JoinHint.Inner;
            relEligible.Add(relUiOpWriteOffWithUiOpCase);

            PredicateExpression predExp = new PredicateExpression();
            predExp.Add(UiOpCaseFields.WriteoffDt >= DateUtil.GetFirstDayOfQuarter(batchDate));
            predExp.Add(UiOpCaseFields.WriteoffDt <= DateUtil.GetLastDayOfQuarter(batchDate));

            eligibleOpCases = TypedListDAO.GetMultiAsDataTable(fieldsEligible, 0, null, predExp, relEligible, true, groupByEligible, 0, 0);

Here you should use a DBFunctionCall:

fieldsEligible.DefineField(UiOpCaseFields.WriteoffDt, 2);
...
var monthExpr = new DBFuncTionCall("MONTH", new object[] { UiOpCaseFields.WriteoffDt });
fieldsEligible[2].ExpressionToApply = monthExpr;
...

Getting in the Field is working fine, But My problem is I need to replicate then same expression into Group by which is throwing the Error. Do you have any idea ?

I am doing as below

ResultsetFields fieldsEligible = new ResultsetFields(3); fieldsEligible.DefineField(UiOpWriteoffDtlFields.OvrpymntCaseAdjstmntAm, 0, AggregateFunction.Sum); fieldsEligible.DefineField(UiOpWriteoffDtlFields.OpCaseFeesAm, 1, AggregateFunction.Sum); fieldsEligible.DefineField(UiOpCaseFields.WriteoffDt, 2,"WDate");

            fieldsEligible[2].ExpressionToApply = new DbFunctionCall("TO_CHAR", new object[] { UiOpCaseFields.WriteoffDt, "MM" });

GroupByCollection groupByEligible = new GroupByCollection(); groupByEligible.Add(fieldsEligible[2]);

I am getting the below Error:

An exception was caught during the execution of a retrieval query: ORA-00979: not a GROUP BY expression. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Which LLBLGen Pro runtime library version are you using?

LLBLGen Pro v3.5

The problem is that the TO_CHAR DB function uses constants as parameters. When you pass them as parameters, it fails. The solution is to pass them as constants into the DBFunctionCall. Aprox fixed code:

...
fieldsEligible[2].ExpressionToApply = new DbFunctionCall("TO_CHAR({0}, MM)", new object[] { UiOpCaseFields.WriteoffDt});
...

Thanks daelmo:-)

But can you please let me know how I can apply the same expression into Group BY

Please check the forum guidelines to know how to get the exact and correct RTL build no. https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7725

Please make sure you are using the latest released v.3.5 out there. Also if you can test it with the latest v.4.1 too.

I am using V3.5 only, I am sorry about that I have not go throw the rule of this forums, But My problem is solved yet.

I am not able to use the expression with Group by I mention my code above.

To use it in the groupBy, just add that field object to the group-by collection. This is my working test using latest v3.5:

[TestMethod]
public void GroupByExpreesionDynList()
{
    // define fields
    var fields = new ResultsetFields(2);
    fields.DefineField(OrderFields.OrderDate, 0);
    fields.DefineField(OrderFields.Freight, 1, AggregateFunction.Sum);

    // set the expression (DATEADD)
    fields[0].ExpressionToApply = new DbFunctionCall("DATEADD(yy, 1, {0})", new object[] { OrderFields.OrderDate });

    // groupBy
    var grouper = new GroupByCollection();
    grouper.Add(fields[0]);

    // fetch
    var results = new DataTable();
    using (var adapter = new DataAccessAdapter())
    {
        adapter.FetchTypedList(fields, results, null, 0, null, true, grouper);
    }
}

Generated SQL

SELECT DATEADD(yy,
               1,
               [Northwind].[dbo].[Orders].[OrderDate]) AS [OrderDate],
       SUM([Northwind].[dbo].[Orders].[Freight])       AS [Freight]
FROM   [Northwind].[dbo].[Orders]
GROUP  BY DATEADD(yy,
                  1,
                  [Northwind].[dbo].[Orders].[OrderDate]) 

Give it a try, it shouldn't fail. If it does, please paste the exact code you are working, and make sure you are using the latest v3.5 version.

That is working fine, Thanks a lot for your help and support