Re-sorting a table array.

From: Tom Deseamus <tdeseamus_at_digital-hire.com>
Date: Wed, 15 Mar 2000 09:01:45 -0600
Message-ID: <sBNz4.6664$_P5.1428496_at_news2.mia>

I have created a table type array based on a select statement, then derived a value for an element in that array. Does anyone know how I can re-sort the array based on the new value. I have listed my code below if it helps.

Thanks, Tom

candidate_index := 0;

    i := 0;
   BEGIN
    FOR C IN
    (SELECT distinct applicant_id, first_name, last_name, keyskills

     FROM   search_candidates_view
     WHERE  upper(nvl(first_name, '%')) like upper(v_first_name)
     AND    upper(nvl(last_name, '%')) like upper(v_last_name)
     AND    nvl(years_of_exp, 0) >= to_number(v_years_of_exp)
     AND    upper(nvl(employer_name, '%')) like upper(v_employer_name)
     AND  upper(nvl(degree, '%')) like upper(v_degree)
     AND    upper(nvl(field_of_study, '%')) like upper(v_field_of_study)
     AND  nvl(gpa, 0) >= to_number (v_gpa)
     AND   ( upper(nvl(home_city, '%')) like upper(v_city)
           or upper(nvl(work_city, '%')) like upper(v_city) )
     AND  ( upper(nvl(home_state, '%')) like upper(v_state)
          or upper(nvl(work_state, '%')) like upper(v_state) )
     AND  ( upper(nvl(home_zip_code, '%')) like upper(v_zip)
          or upper(nvl(work_zip_code, '%')) like upper(v_zip ))
     AND  upper(nvl(job_title, '%')) like upper(v_job_title)
     AND  ( upper(nvl(keyskills, '%')) like upper(keyskill(1))
      OR  upper(nvl(keyskills, '%')) like upper(keyskill(2))
      OR  upper(nvl(keyskills, '%')) like upper(keyskill(3))
      OR  upper(nvl(keyskills, '%')) like upper(keyskill(4))
      OR  upper(nvl(keyskills, '%')) like upper(keyskill(5))
      OR  upper(nvl(keyskills, '%')) like upper(keyskill(6))
      OR  upper(nvl(keyskills, '%')) like upper(keyskill(7))
      OR  upper(nvl(keyskills, '%')) like upper(keyskill(8))
      OR  upper(nvl(keyskills, '%')) like upper(keyskill(9))
      OR  upper(nvl(keyskills, '%')) like upper(keyskill(10)))
      AND  ( upper(nvl(position_applied_for, '%')) like upper(position(1))
      OR  upper(nvl(position_applied_for, '%')) like upper(position(2))
      OR  upper(nvl(position_applied_for, '%')) like upper(position(3))
      OR  upper(nvl(position_applied_for, '%')) like upper(position(4))
      OR  upper(nvl(position_applied_for, '%')) like upper(position(5))
      OR  upper(nvl(position_applied_for, '%')) like upper(position(6))
      OR  upper(nvl(position_applied_for, '%')) like upper(position(7))
      OR  upper(nvl(position_applied_for, '%')) like upper(position(8))
      OR  upper(nvl(position_applied_for, '%')) like upper(position(9))
      OR  upper(nvl(position_applied_for, '%')) like upper(position(10)))
     order by last_name)

    LOOP

     searchitems := 0;
     hits := 0;


     if keyskill(1) IS NOT NULL  then
       searchitems := searchitems + 1;
       if upper(nvl(c.keyskills, '%')) like upper(keyskill(1)) then
        hits := hits + 1;
       end if;
      end if;

   if keyskill(2) is not null then
       searchitems := searchitems + 1;
        if upper(nvl(c.keyskills, '%')) like upper(keyskill(2)) then
        hits := hits + 1;
     end if;

   end if;

   if keyskill(3) is not null and keyskill(3) != keyskill(1) then

       searchitems := searchitems + 1;
        if upper(nvl(c.keyskills, '%')) like upper(keyskill(3)) then
        hits := hits + 1;
     end if;

   end if;

   --htp.print(keyskill(3));

   if searchitems = 0 then
    hitratio := 100;
   else
    hitratio := (hits / searchitems) * 100;    end if;

     applicantsDataID.EXTEND;
     i := i+1;
     applicantsDataID(i).applicant_id   := C.applicant_id;
     applicantsDataID(i).applied_job_id  := null;
     applicantsDataID(i).first_name    := C.first_name;
     applicantsDataID(i).last_name    := C.last_name;
     applicantsDataID(i).score       := hitratio;
     applicantsDataID(i).selected    := 0;

    END LOOP;
   EXCEPTION

     WHEN OTHERS THEN
     formSuper.setStatusLine( 'Candidates database search: ' || SQLERRM );

   END; Received on Wed Mar 15 2000 - 16:01:45 CET