Re: Forms 3.0 - changing order by in a block

Licensed User <z9daspit_at_kzo.us.pnu.com> wrote:
: Sorry for bring up something from the dark ages, but...

: I have a forms 3.0 block based on table. I can use (bind) variables in
: defining
: the block in the WHERE clause, but can't get them to work in the ORDER BY
: clause. Is this posible? Is there any other way to change the sort order
: of
: a table-based block at run-time?

: TIA
: Dwight

This topic is covered by the 'Advanced SQL*Forms Techniques' documentation, in the section 'Performing Dynamic Sorting'.

Here is an example from one of my own forms utilizing this technique. The trick is to assign the order by condition to a hidden forms field variable using the #-symbol which - as far as I understand - passes the string after the symbol directly to the sql-parser. You'll notice that the forms block doesn't contain an order by clause.

You must be careful that the length of the generated order by clause doesn't exceed the query_length of the forms variable.

   DEFINE BLOCK

      NAME = RECIPIENT
      DESCRIPTION = RECIPIENT
      TABLE = HJERTE.RECIPIENT
      ROWS_DISPLAYED = 14
      ROWS_BUFFERED = 14
      BASE_LINE = 8
      LINES_PER_ROW = 1
      ARRAY_SIZE = 0

      DEFINE TRIGGER

         NAME = PRE-QUERY
         TRIGGER_TYPE = V3
         TEXT = <<<
         DECLARE
           sort_string CHAR(255) :=
             '#= cmv_code or cmv_code is null) order by (';
         BEGIN
           IF :start.re_query_patient = 'TRUE' AND
              :start.scandianumber IS NOT NULL
           THEN
              :recipient.scandianumber := :start.scandianumber;
           END IF;
           IF :recipient.date_of_birth_format IS NOT NULL THEN
             transf_char_date(:start.country_code,
                              :recipient.date_of_birth_format,
                              :recipient.date_of_birth);
           END IF;
           IF :sorting.sort_code = 'S' THEN
             sort_string := sort_string||'Scandianumber';
           ELSIF :sorting.sort_code = 'B' THEN
             sort_string := sort_string||'Date_Of_Birth';
           ELSIF :sorting.sort_code = 'N' THEN
             sort_string := sort_string||'Status';
           ELSE
             sort_string := sort_string||'Scandianumber';
           END IF;
           :recipient.cmv_code := sort_string;
         END;
         >>>

      ENDDEFINE TRIGGER
...
      DEFINE FIELD

         NAME = CMV_CODE
         DATATYPE = CHAR
         LENGTH = 255
         DISPLAY_LENGTH = 1
         QUERY_LENGTH = 255
         DISPLAYED = OFF
         PAGE = 1
         LINE = 1
         COLUMN = 79
         ECHO = OFF
         INPUT = OFF
         UPDATE = OFF
         QUERY = OFF

      ENDDEFINE FIELD

   ENDDEFINE BLOCK

-- 
Christian Mondrup, Computer Programmer
Scandiatransplant, Skejby Hospital, University Hospital of Aarhus
Brendstrupgaardsvej, DK 8200 Aarhus N, Denmark
Phone: +45 89 49 53 01, Telefax: +45 89 49 60 07