oracle-databasesortingplsqloracleforms

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


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


Solution

  • 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 DESC;
    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.