I have consulted all posts on this forum concerning the ODP.net arithmentic overflow problem.

including...this. http://llblgen.com/TinyForum/Messages.aspx?ThreadID=4543&HighLight=1

I am getting this error and I am using the latest version 1.0.2005.1, and ODP.net for oracle 10g.

I've observed my sql trace and it is indeed specifying a truncation of 28 in the sql call, however I still get the arithmetic overflow error. Shouldn't the trunction take care of the problem?

Also, there is this: http://www.oracle.com/technology/sample_code/tech/windows/odpnet/SafeTypeMapping/readme.html from oracle. Not too sure what to make of it from the vantage point of using LLBL Gen Pro.

The actual LLBL code is a dynamic list with aggregate field using avg. The call works for Count, but gives up the error on avg.

Thanks

Puromtec wrote:

I have consulted all posts on this forum concerning the ODP.net arithmentic overflow problem.

including...this. http://llblgen.com/TinyForum/Messages.aspx?ThreadID=4543&HighLight=1

I am getting this error and I am using the latest version 1.0.2005.1, and ODP.net for oracle 10g.

I've observed my sql trace and it is indeed specifying a truncation of 28 in the sql call, however I still get the arithmetic overflow error. Shouldn't the trunction take care of the problem?

Unfortunately, not always. There are situations where this problem still occurs. Oracle refuses to make truncate the value, they say it's better to throw an exception.

Also, there is this: http://www.oracle.com/technology/sample_code/tech/windows/odpnet/SafeTypeMapping/readme.html from oracle. Not too sure what to make of it from the vantage point of using LLBL Gen Pro.

The actual LLBL code is a dynamic list with aggregate field using avg. The call works for Count, but gives up the error on avg.

This is because avg results in a NUMBER which overflows System.Decimal, and this gives the error. This exception occurs inside ODP.NET's datareader code, and this thus means that fetching a datatable with ODP.NET using oracledataadapter (which is used in a dyn. list fetch) gives this exception as well.

It's very unfortunate, and hard to avoid. In v2 I've added code to the generic fetch logic which allows you to override a method when such an exception occurs during entity fetches, however for datatable fetches using dataadapters, it's out of my hands.

What you could try is to implement IExpression to call a truncate function on hte field which you avg. See for an example how to implement IExpression to call a DB function:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829

In v2 this is build in so it's simple in v2, but in v1, you've to implement it yourself. You then should get something as SELECT TRUNCATE(AVG(field),...)

Cannot seem to apply my own expression around the field. Database trace still says "Trunc(Avg(field),28)" and I still get the arithmetic overflow error.

My goal in this test is to contain the field aggregate/trunc with a Floor function

e.g. "Floor(Trunc(Avg(field),28)"

Below is the calling code that I am using. The IExpression implementation is the same as yours. Any idea why my Floor isn't getting appended to the sql statement?

//create a filter predicate added to f (the predicate expression)

f.Add(PredicateFactory.CompareValue(Hello_C95FieldIndex.Pmp, ComparisonOperator.GreaterEqual, val));

IPredicateExpressionElement p = (IPredicateExpressionElement)f[0];

        FieldCompareValuePredicate fp = (FieldCompareValuePredicate)p.Contents;



        IExpression trun = new F18DAL.FunctionExpression(EntityFieldFactory.Create(Hello_C95FieldIndex.Pmp),
            "Floor");

        fp.FieldCore.ExpressionToApply = trun;

        //using the parentheses and the logic operators, organize the p.ex's



        ResultsetFields fields = new ResultsetFields(3);
        fields.DefineField(Hello_C95FieldIndex.Pmp, 0, "PmpAvg", "Hello_C95", AggregateFunction.Avg);
        fields.DefineField(FlightsynFieldIndex.Name, 1, "Name");
        fields.DefineField(FlightsynFieldIndex.Segmentstart, 2, "Segmentstart");


        IRelationCollection relations = new RelationCollection();
        relations.Add(FlightsynEntity.Relations.RecsynEntityUsingFlightid);
        relations.Add(RecsynEntity.Relations.Hello_C95EntityUsingRecordid);


        IGroupByCollection groupByClause = new GroupByCollection();
        groupByClause.Add(fields[1]);
        groupByClause.Add(fields[2]);



        DataTable dynamicList = new DataTable();

        TypedListDAO dao = new TypedListDAO();


        dao.GetMultiAsDataTable(fields, dynamicList, 0, null, f, relations, true, groupByClause, null, 0, 0);

---Thanks

Ok, i've got the answer.

1. I cannot use the: fields.DefineField(Hello_C95FieldIndex.Pmp, 0, "PmpAvg", "Hello_C95", AggregateFunction.Avg); to set the Avg. I must define the field like so: fields.DefineField(Hello_C95FieldIndex.Pmp, 0, "PmpAvg");

2. Alter the IExpression implementation to handle two function expressions. Floor(Avg(x))

3. Add this line of code to set the expression on the field, as opposed to the filter

fields[0].SetExpression(trun) //setting up fields for dynamic list

If i left the aggregate function in the define field statement and set custom expression i will have a problem described below:

SQL side: TRUNC(AVG(FLOOR("MF"."HELLO_C95"."PMP")) -- which does nothing new

In fact you use 2 expressions: EXPRESSION_A(EXPRESSION_B)

where EXPRESSION_A is your function emitting expression and expression B is a field with an aggregate function set, AVG.