Re: Forms 3.0: Sort on non-db field, how-to

Haakon T. Soenderland (hts_at_sasdata.no) wrote:
: In article <48ajl3$4a2_at_lll-winken.llnl.gov>, 68a869_at_llnl.gov says...
: >
: >I have a forms 3.0 form which has a base-table block for an employee
: >profile. The table contains an employee ID, and the employee name is
: >retrieved in a POST-QUERY trigger.
: >
: >Does anyone know how I can sort the records on the employee name?
 

: Using the setup you have there is no way to do this. The only way is
: to change the base block to go against a view that includes the employee
: name. Then you would have to handle inserts, updates and deletes in your
: own trigger code.

I have had a similar problem with a pop-up block containing a limited set of records. My users want to see the records in a specified order. Because of the limited set of records I was able to circumvent the problem thus: In a KEY-STARTUP trigger I call a procedure like this:

   get_infect_sort_order(:start.infect_sort_order);

The procedure loops through a code domain table containing a code value plus a sort order value for each row and builds up a sort order string for later use.

   DEFINE PROCEDURE

      NAME = get_infect_sort_order
      DEFINITION = <<<
      PROCEDURE get_infect_sort_order(sort_order IN OUT CHAR) IS
        CURSOR c1 IS
          SELECT
            ''''||code||'''' code,
            TO_CHAR(sort_info) sort_info
          FROM eng_serology
          ORDER BY sort_info;
      BEGIN
        sort_order := 'DECODE(Code,';
        FOR c1_rec IN c1 LOOP
          sort_order := sort_order||c1_rec.code||','||c1_rec.sort_info||',';
        END LOOP;
        sort_order := SUBSTR(sort_order, 1, LENGTH(sort_order)-1)||')';
      EXCEPTION
        WHEN others THEN
          proc_err('get_infect_sort_order');
      END;
      >>>

   ENDDEFINE PROCEDURE the field :start.infect_sort_order belongs to a house keeping control block.

Then in the PRE-QUERY trigger for the pop-up block I force the sorting of the records using the sort order string:

      DEFINE TRIGGER

         NAME = PRE-QUERY
         TRIGGER_TYPE = V3
         TEXT = <<<
         BEGIN
           :infection_serology.sort_info :=
             '#=sort_info or sort_info is null) order by ('
             ||:start.infect_sort_order;
         END;
         >>>

      ENDDEFINE TRIGGER

Of course this solution only works for small sets of records.

: Regards,
: Haakon
: --
: hts_at_sasdata.no | htsoend_at_ibm.net | Haakon T. Soenderland
: Scandinavian Airlines Data Norway A/S
: ---
: "40 skiver og et herpa anlegg, en leilighet som trenger aa spyles. Ingen
: venner og ingen penger, alt jeg har er mine klamme hender.." Jokke '94
: ------------------------------------------------------------------------
: Windows 95: A another first from Microsoft!
: The first OS to be obsolete *before* it was released.

--
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+      Christian Mondrup                                                      +
+      UNI-C Aarhus, Danish Computing Centre for Research and Education.      +
+      Olof Palmes Alle 38, DK 8200 Aarhus N.                                 +
+                                                                             +
+      Phone:   +45 86 78 44 44                                               +
+      Telefax: +45 86 78 44 55                                               +
+      E-Mail:  Christian.Mondrup_at_uni-c.dk                                    +
+                                                                             +
+      Opinions expressed are mine and do not reflect those of my employer.   +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++