how to sort by different attribute each time we click a button pl/sql?-CodePudding

When I click the button for the first time, my selected column is sorted. When I click for the second time, it will sort in reverse.How can I do this? Note: My program consists of dummy block. No data blocks

declare
  cursor c is
    select *
      from muhasebe.doviz_takip
     where UPPER(fatura_no) LIKE
           NVL('%' || UPPER(:giris.sorgulama) || '%', UPPER(fatura_no))
     order by fatura_no asc;
begin
  go_block('XDOVIZ_TAKIP');
  clear_block;
  first_record;

  for r in c loop  
    :FATURA_NO   := r.fatura_no;
    :ACIKLAMA    := r.aciklama;
    :YUKLEME_TAR := r.yukleme_tar;
    :VARIS_TAR   := r.varis_tar;
    :TUTAR       := r.tutar;  
    next_record;  
  end loop;

  first_record;
end;

This is the code I can sort once

CodePudding user response：

One option is to create a global variable (or a parameter), two cursors (one for each sorting) and IF-THEN-ELSE which decides which cursor to use, depending on global variable's value.

Something like this:

DECLARE
   CURSOR c_asc IS
        SELECT *
          FROM muhasebe.doviz_takip
         WHERE UPPER (fatura_no) LIKE
                  NVL ('%' || UPPER ( :giris.sorgulama) || '%',
                       UPPER (fatura_no))
      ORDER BY fatura_no ASC;

   CURSOR c_desc IS
        SELECT *
          FROM muhasebe.doviz_takip
         WHERE UPPER (fatura_no) LIKE
                  NVL ('%' || UPPER ( :giris.sorgulama) || '%',
                       UPPER (fatura_no))
      ORDER BY fatura_no ASC;
BEGIN
   GO_BLOCK ('XDOVIZ_TAKIP');
   CLEAR_BLOCK;
   FIRST_RECORD;

   :global.sort := NVL ( :global.sort, 'ASC');

   IF :global.sort = 'DESC'
   THEN
      FOR r IN c_asc
      LOOP
         :FATURA_NO := r.fatura_no;
         :ACIKLAMA := r.aciklama;
         :YUKLEME_TAR := r.yukleme_tar;
         :VARIS_TAR := r.varis_tar;
         :TUTAR := r.tutar;

         NEXT_RECORD;
      END LOOP;

      :global.sort := 'ASC';
   ELSIF :global.sort = 'ASC'
   THEN
      FOR r IN c_desc
      LOOP
         :FATURA_NO := r.fatura_no;
         :ACIKLAMA := r.aciklama;
         :YUKLEME_TAR := r.yukleme_tar;
         :VARIS_TAR := r.varis_tar;
         :TUTAR := r.tutar;

         NEXT_RECORD;
      END LOOP;

      :global.sort := 'DESC';
   END IF;

   FIRST_RECORD;
END;

Perhaps you could try to make it "smarter" (as this is pretty much dummy - repeating more or less the same code twice), but - this is simple and easy to maintain.

CodePudding user response：

One another approach would be converting it to a database block by creating a view named v_doviz_takip such as

CREATE OR REPLACE v_doviz_takip AS
SELECT *
  FROM muhasebe.doviz_takip

and setting the Query Data Source Name of the block xdoviz_takip to this and adding a WHERE clause

UPPER(fatura_no) LIKE NVL('%' || UPPER(:giris.sorgulama) || '%', UPPER(fatura_no))`

to the block. Then add a line

SET_BLOCK_PROPERTY('xdoviz_takip',order_by, 'fatura_no');

to the WHEN-NEW-FORM-INSTANCE trigger.

And convert your current code block to this one :

DECLARE
  v_ord  VARCHAR2(25) := GET_BLOCK_PROPERTY('XDOVIZ_TAKIP',order_by);
BEGIN
  GO_BLOCK ('XDOVIZ_TAKIP');  
  CLEAR_BLOCK;
  IF v_ord = 'fatura_no' THEN v_ord := v_ord||' DESC'; ELSE v_ord := 'fatura_no' END IF;  
  SET_BLOCK_PROPERTY('XDOVIZ_TAKIP',order_by, v_ord);
  EXECUTE_QUERY;
  FIRST_RECORD;
END;

this way, it will sort descendingly by fatura_no for the first, and ascendingly for the second attempts , or just converting the current IF clause to IF v_ord != 'fatura_no' ... would reversely change the behaviour.