Re: read backwards?

In article <3vljdh$pi9_at_calweb.calweb.com>, rdugaue_at_web1.calweb.com (Robert Du Gaue) writes:
|> We have a database with appoximately 500,000 rows. One query allows users
|> to bring up a list of names so that they can narrow down to a specific
|> data record. When the list is up, we want them to have the ability to
|> page forward and backward. The forward works fine, however the backward query
|> using a 'where name < first_displayed_name' type of query goes back to
|> the begining of the table and basically reads 1,000s of rows.
|>
|> Is there a way to read backwards from a certian point and return say the
|> next 25 names before a specific key and not have ORacle start from the
|> begining of the table?

There's a way to simulate this kind of behavior, but it's tricky. Here's a sketch. I assume a base table and block called PEOPLE with a single key field NAME, and a control block called CONTROL.

    <<< block PEOPLE trigger KEY-PRVREC >>>     BEGIN
    if :system.cursor_record = '1' then

• we're at the first record of the latest data set
• retrieved, but the user wants to move to the
• record "before" this one
• function GET_PREV_NAME takes a NAME and uses some
• method to pick a NAME that would be earlier than
• the current NAME
• it could be as simple as taking SMITH and returning
• RMITH (decrementing the initial), or as precise as
• opening a cursor of { NAME < the current NAME }
• with "order by NAME desc", and finding the particular
• NAME that is, say, 25 rows earlier than the current NAME
• however the function works, put the current and
• "previous" name into control fields :control.curr_name := :people.name; :control.prev_name := get_prev_name( :people.name ); if :control.prev_name = :control.curr_name then
• if the most "previous" NAME the function could find
• was the current NAME, then we're at the first row of
• the entire table and can't back up any more :control.prev_name := null; message( 'At first record.' ); raise form_trigger_failure; end if;
• (handle pending updates, etc., here) ...
• (see <<< block PEOPLE trigger PRE-QUERY >>> below) execute_query; chk_package_failure;
• move from the first record of this new data set down
• to the first record of the last data set loop if :people.name = :control.curr_name then
• we're back to the database row where we started, but
• :system.cursor_record is now '26' or '99' or something exit; elsif :system.last_record = 'TRUE' then message( 'Error: can''t find "'||:control.curr_name||'"' ); raise form_trigger_failure; else next_record; end if; end loop;
• [else] we're not at record #1 end if; previous record; END;

  ---

  <<< block PEOPLE trigger PRE-QUERY >>> BEGIN if :control.prev_name is not null then
• block PEOPLE trigger KEY-PRVREC gave us a criterion
• for the current query copy( '#>='''||:control.prev_name||'''', 'people.name' ); :control.prev_name := null; end if; END;

  ---

  If your NAME key is not unique, you might have to throw in consideration of ROWIDs. You could also tweak this if your key was actually ( LAST_NAME , FIRST_NAME ) or something more complex.

By the way, once I demonstrated the admittedly jerky behavior of this method, my supervisors decided they didn't need this kind of functionality after all, and would just make users re-query with different criteria. But it can be done.

Good luck. Received on Wed Aug 02 1995 - 00:00:00 CEST