Re: Forms 4.5 Running Totals

From: Steven P. Muench <smuench_at_oracle.com>
Date: 1995/07/09
Message-ID: <SMUENCH.95Jul8190453_at_doh.oracle.com>

Forms is smart about fetching only enough records to show the user the initial screenful. Each record fetched fires the POST-QUERY trigger once for that row. So, in effect, unless you force all records to be queried, while your running total *does* reflect the sum of the records that have been fetched so far, it doesn't do what you want which is show the sum for the set of records that their current query criteria identified, without actually forcing all of the records to be queried.

One idea would be to take advantage of the following facts:

    (*) The variable :SYSTEM.LAST_QUERY holds the SELECT statement

        that Forms last executed to retrieve records for the most
        recently queried block. In particular, if you search
        in that string for the word "WHERE", then everything after
        that represents the where clause predicate that was
        constructed by Forms using the query-by-example criteria
        the user (might have) supplied, as well as any default
        Where clause. For simplicity, I'm assuming there is
        no default Order By clause. If there is, you'd have to
        chop that part off at the end of the string by searching
        the string (using INSTR in PL/SQL) for the "ORDER BY"
        keywords.

    (*) Create_Group_From_Query() is a built-in that let's you
        specify a string (any valid SELECT statement) on which
        to build a record group. Using this fact, you can
        pass it any SELECT statement, and then process the
        one or more records that come back as a result using
        the standard Record Group built-in commands, as if
        it were an in-memory table or array.

So, combining these two ideas, you could in your PRE-SELECT trigger, get the WHERE clause that's about to be used to retrieve the result set for the block, pass it to a function that you write that "glues" together a string like:

      'SELECT SUM(<your_columnname>) THE_SUM FROM <yourtable> WHERE'

with the WHERE clause you pulled out of the :SYSTEM.LAST_QUERY above, and then uses the concatenated string (which now represents a valid SELECT statement to let the database calculate the sum), and use the Create_Group_From_Query() command, followed by the Populate_Group to kick off the record group's query, to get back a record group with one column (named THE_SUM, after the column alias above) and one row that will contain the sum across the records you want.

Can't vouch for perfect syntax, since I don't have the on-line help on my laptop right now, but this function might look something like:

    FUNCTION CalculateColumnSum( the_table VARCHAR2,
                                 the_column VARCHAR2,
                                 the_whereClause VARCHAR2)
    RETURN NUMBER IS
      rg   RecordGroup;
      rc   NUMBER;
      qs   VARCHAR2;
      done EXCEPTION;
    BEGIN


      --

      -- Prepare the query string


      --

      qs := 'SELECT SUM('||the_column||') THE_SUM '||
            'FROM '||the_table||' WHERE '||
            the_whereClause;


      --

      -- Create a record group on the fly with 'qs' as its

      -- query


      --

      rg := Create_Group_From_Query('tempname', qs);

      rc := Populate_Group(rg);

      IF (rc <> 0) THEN
        -- 
        -- Some SQL error occurred attempting to populate
        -- the query
        --
        rc := 0;
        RAISE done;
      END IF;


      --

      -- The sum will be in the first row of the

      -- 'tempname' record group, in the (only) column

      -- in that group named 'the_sum'


      --

      rc := Get_Group_Number_Cell('tempname.the_sum',1);
      RAISE done;
    EXCEPTION
      WHEN done THEN
        Delete_Group(rg);
        RETURN rc;  

    END; If you're concerned with network traffic, you could probably save a roundtrip or two by implementing the dynamic querying of the sum in a stored procedure that prepares and executes the query using the features of the DBMS_SQL package (in Oracle7, release 7.1 and later).

Hope this gets some ideas going...

---

Steve Muench Email: smuench_at_oracle.com Oracle Corporation

%>In article <3tll5r$ino_at_zorn.mnet.medstroms.se> Pauli Salmu <p.salmu_at_mn.medstroms.se> writes:
%>
%>   Path: inet-nntp-gw-1.us.oracle.com!gatekeeper.us.oracle.com!decwrl!hookup!swrinde!howland.reston.ans.net!vixen.cso.uiuc.edu!news.uoregon.edu!psgrain!fizban.solace.mh.se!vampire.xinit.se!newsfeed.tip.net!zorn.mnet.medstroms.se!news
%>   From: Pauli Salmu <p.salmu_at_mn.medstroms.se>
%>   Newsgroups: comp.databases.oracle
%>   Date: 8 Jul 1995 10:03:39 GMT
%>   Lines: 23
%>   References: <950707090451_702420.204300_BHD54-29_at_CompuServe.COM>
%>   NNTP-Posting-Host: 10.ts1.mnet.medstroms.se
%>   Mime-Version: 1.0
%>   Content-Type: text/plain; charset=iso-8859-1
%>   Content-Transfer-Encoding: 8bit
%>
%>   Graeme_King_at_tjhouse.ccmail.compuserve.com wrote:
%>   >
%>   >      Can anybody supply a solution to this problem?
%>   > 
%>   >      I have a multi-record block (displaying 5 records) based on a table,
%>   >      called for example  EMP. The block has an item called SALARY in it.
%>   > 
%>   >      I want a text item (on a different block) which is a total of the
%>   >      SALARY column for all records queried (which may be one, many or all
%>   >      of the records in the table EMP).
%>   > 
%>   >      I tried a POST-QUERY trigger to calculate the total salary but it only
%>   >      fired 5 times i.e for each displayed record although there were more
%>   >      than 5 that meet the query criteria. When I scrolled down one record
%>   >      it fired again and updated the total salary. No good.
%>   > 
%>
%>   A quick fix: keep your post-query like that and program key-exeqry
%>   of the queried block like this:
%>
%>   execute_query;
%>   last_record;

--
.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,
  Steve Muench              Email: smuench_at_us.oracle.com
  Sr. Product Manager
  Tools Development
  Oracle Corporation

Received on Sun Jul 09 1995 - 00:00:00 CEST