Re: Need SQL*Forms 3 trigger help.

In article <DBzt1r.7vD_at_iglou.com>, proberts_at_iglou.iglou.com (Phil Roberts) writes:
|> <stuff deleted>
|> The sorted table looks something like this:
|>
|> SSN AWARD AWARD DATE
|> --------- -------- ----------
|> 123456789 AAAAAAAA 12-FEB-95
|> 123456789 BBBBBBBB 24-MAY-94
|> 123456789 BBBBBBBB 12-JUL-95
|> 234567890 BBBBBBBB 07-APR-93
|> 234567890 BBBBBBBB 13-JUL-95
|> 234567890 CCCCCCCC 19-JAN-95
|>
|> On the form I want it displayed something like this:
|> |-------------------------------------------------------|
|> | SSN |
|> | --------- |
|> | 123456789 |
|> | NUMBER |
|> | LAST DATE OF TIMES |
|> | AWARD RECEIVED RECEIVED |
|> | -------- --------- ------ |
|> | AAAAAAAA 12-FEB-95 1 |
|> | BBBBBBBB 12-JUL-95 2 |
|> |-------------------------------------------------------|

Someone already suggested a view for the multi-line part of your display. The SELECT statement would be

     select ssn, award, max(award_date), count(*)
     from   <table>
     group by  ssn, award ;

There's a trickier way to handle this without views.

1. Base your detail block, AWARDS, on the table that you gave sample rows for, ordering by AWARD, ascending, and AWARD_DATE, *descending* .
2. Put the base-table fields :AWARDS.AWARD and :AWARDS.AWARD_DATE on-screen, and define an on-screen non-base-table field called :AWARDS.COUNT .
3. In a control block, define a hidden field, :CTRL.LAST_AWARD .
4. In the block AWARDS trigger PRE-QUERY, (re-)set the hidden control field to NULL.
5. In the block AWARDS trigger POST-QUERY, say

     if :awards.award = :ctrl.last_award  then
       raise form_trigger_failure;
     else
       select   count(*)
         into   :awards.count
         from   AWARDS
         where  award = :awards.award
           and  ssn   = :awards.ssn ;
       :ctrl.last_award := :awards.award;
     end if;

   The first row that crops up for a given SSN and AWARD    will have the "Last Date Recieved" for the award,    since you ordered by AWARD_DATE *descending*. On the    screen, this record represents all rows for its SSN    and AWARD. Its :AWARDS.COUNT field is the number of    those rows. Subsequent rows for the same SSN and    AWARD, with the same or earlier date, are rejected    from being displayed.

5. Instead of using the PRE- and POST-QUERY triggers,

   you could try to use the WHERE/ORDER BY clause to    only select those records that have the latest date    for their SSN and AWARD, but you'd have to bring the    ROWID into it, in case multiple rows have the lastest    date. I think this implies a sub-query with its own    sub-sub-query, which I would guess is not as efficient    as just fetching rows and weeding them with the    POST-QUERY. Or you could have a single sub-query in    the WHERE/ORDER BY and then weed duplicate occurences    of the latest date with triggers as above.

Either method, the base-table view or the one-record- standing-for-many, presents challenges if you want the user to be able to make changes through the block.

The big advantage I see with the latter method is, in a development environment where you have to ask someone else to create database objects for you, you don't need one.

Good luck. Received on Fri Jul 21 1995 - 00:00:00 CEST