Re: Sorting

From: Matt B. <mcb_at_fightspam.sd.znet.com>
Date: Tue, 10 Oct 2000 19:59:46 -0700
Message-ID: <su7mob845vj13a_at_corp.supernews.com>

<lopezrichie_at_my-deja.com> wrote in message news:8rt5at$eu9$1_at_nnrp1.deja.com...
> We are trying to make a sort order functionality in our forms. For example ;
> when a user is on a certain record and he/she wants to order by another
> field, he/she presses this sort order button. The code behind the button sets
> the block property order_by to order by the other field. Then it executes
> query( as you know the new sort order does not work until you execute query).
> Then it loops so it can fall on the same record it was on. This works but on
> a table with 32000 records, it runs pretty slow. Does anybody have any ideas?

Probably not much. What you're probably doing is saving the "unique" values of the row you are on in a control block item(s) or something like that and after the query runs under the new order by I guess you are looping through the records with NEXT_RECORD until you find a match, right? Doesn't sound like there is that much you can do.

Maybe (this is a stretch and a total guess and might not be worth it) you can go a little bit towards not having to re-loop every time from the first record.

Let's say the user is sorting on columns 1 and 2 and they want to sort on columns 1 and 3.

1. Store the record number they're on in a control item.
2. Compare the existing where clause you have now and the soon-to-be-new where clause.
3. If you can determine the first column in the ORDER BY for both is the same, then PREVIOUS_RECORD to the first record in the primary sort criteria by PREVIOUS_RECORDing to the last one in the previous grouping:

v_record_to_goto_after_query NUMBER := 1; v_old_col1_value VARCHAR2 := :MYBLOCK.COL1; BEGIN
IF LTRIM(RTRIM(SUBSTR(v_old_where,1,(INSTR(v_old_where,',')-1)))) = LTRIM(RTRIM(SUBSTR(v_new_where,1,(INSTR(v_new_where,',')-1)))) AND :SYSTEM.CURSOR_RECORD > 1 THEN
   WHILE :SYSTEM.CURSOR_RECORD > 1 AND v_old_col_1_value = :MYBLOCK.COL1 LOOP

      PREVIOUS_RECORD;
      IF NOT FORM_SUCCESS THEN
         RAISE FORM_TRIGGER_FAILURE;
      END IF;

   END LOOP;
   --
   IF :SYSTEM.CURSOR_RECORD > 1 THEN
      v_record_to_go_to_after_query := :SYSTEM.CURSOR_RECORD + 1;    END IF;
   v_old_col_1_value := NULL;
   EXECUTE_QUERY;
   IF :SYSTEM.CURSOR_RECORD <> v_record_to_go_to_after_query AND

      v_record_to_go_to_after_query > 1 THEN
   GO_RECORD(v_record_to_go_to_after_query);
   IF NOT FORM_SUCCESS THEN
      RAISE FORM_TRIGGER_FAILURE;

   END IF;
END IF;
v_record_to_go_to_after_query := NULL;
v_old_col_1_value;

Then start your existing looping after this.

This will (hopefully) get you a head start to sort of jump down to the "group" of records you were in beforehand (rather than always starting at the first record) and then from there you loop to the actual record you were on.

Make sense? (Not sure if I explained it well.)

-Matt Received on Wed Oct 11 2000 - 04:59:46 CEST