7 Replies Latest reply: Apr 20, 2013 6:43 AM by 716249 RSS

    Aggregate functions in ADF BC

    716249
      How to use functions like count/sum/min/max from Dept over Emp. Meaning like no. of records in a dept (count) using java code.
      I want something like this but with Java code not through grooovy

      https://blogs.oracle.com/adf/entry/using_groovy_aggregate_functions_in

      TIA
        • 1. Re: Aggregate functions in ADF BC
          Timo Hahn
          In this case you have to either iterate over the records filtered (e.g. child records) or you have to build sql clauses which do the work for you. The second solution is better if you have to e.g. sum up many rows.

          However I'm interesting in why you don't want to use Groovy which already has the function?

          Timo
          • 2. Re: Aggregate functions in ADF BC
            716249
            Actually I am trying to learn few things in Jdev and Java.

            I am able to achieve count through this code ..

            ........................
            RowIterator empi = getEmp1();
            Number n = new Number (empi.getRowCount());
            return n;

            Now I am looking for Sum , Min, Max.

            I know I can do that in Groovy pretty easily and I actually have done that. Its just that I am trying to learn Jdev, so was just exploring it.

            Any idea how can I be able to do that.

            Thanks
            • 3. Re: Aggregate functions in ADF BC
              Timo Hahn
              RowIterator empi = getEmp1();
              Number n = new Number (empi.getRowCount());
              return n;
              this is something you should not do in general. It means that out suck every roe into memory just to count them. It works OK if there are not too many rows involved, however, if you get >1000 rows you'll end up with an out of memory error.
              There is a special method getEstimatedRowCount() which you shoudl use to get the number of rows. This method generates a select count(*) from talbe sql clause to get to the number. This works for big rows sets too.

              Next, there are no build in functions for max, min, sum, avg and so on. You have to either generate a sql statement to let the db do the calculation (which is a best practice from my point of view), or you have to iterate over the rowset and du the calculation in java. This however means that you have to load all rows and you may run into memory problems.

              Advice: stick to groovy or use the sql statement approach.

              Timo
              • 4. Re: Aggregate functions in ADF BC
                716249
                Hey Timo thanks for the reply

                Could you please let me know how to get this method .getEstimatedRowCount()
                I am not able to see this in my empi. methods.

                Also it would be great if you could point me for how "generate a sql statement to let the db do the calculation " or tutorials where I can learn Jdev using Java.

                TIA
                • 5. Re: Aggregate functions in ADF BC
                  Timo Hahn
                  This depends on where you want to get this value. In the UI the tree or table binding expose this method (Count: #{bindings.Employees.estimatedRowCount}) in the model layer you get it via the ViewObject.

                  Timo
                  • 6. Re: Aggregate functions in ADF BC
                    Subramanian Meyyappan
                    Could you please let me know how to get this method .getEstimatedRowCount()
                    Viewlayer also accessing Viewobject you can get it estimatedrowcount method. what about your row count?
                    I am not able to see this in my empi. methods.
                    no you cant.
                    Also it would be great if you could point me for how "generate a sql statement to let the db do the calculation " or tutorials where I can learn Jdev using Java.
                    read two times but i cant get your point inside double "" ""
                    • 7. Re: Aggregate functions in ADF BC
                      716249
                      oh about sql statement , I was trying to ask things which Tim has stated in his second reply to this thread.

                      Thx