The GET_BEST_EORANK function:

-- user-defined function, written by eml 7-8-2003 for Kentucky NHP --
-- designed to retrieve highest EORANK for a given element subnational ID value --
-- treats EOs ranked "E" as better than those ranked "C*" OR "D*" --

CREATE OR REPLACE FUNCTION GET_BEST_EORANK(est_id NUMBER)
RETURN VARCHAR2
IS
d_id NUMBER;
rank varchar2(2);
e_rank NUMBER;
this_rank NUMBER;
CURSOR rank_id IS
SELECT d_basic_eo_rank_id
from eo
where element_subnational_id = est_id
and d_basic_eo_rank_id is not null;
BEGIN
--Find out if there is an E Rank anywhere in the list--
e_rank := 0;
BEGIN
FOR record in rank_id LOOP
FETCH rank_id into this_rank;
EXIT when rank_id%NOTFOUND;
BEGIN
IF this_rank = 14 THEN
e_rank := 1;
ELSE
e_rank := e_rank;
END IF;
END;
END LOOP;
END;

--Collect the lowest rank id for the element--
BEGIN
SELECT min(d_basic_eo_rank_id)
INTO d_id
FROM eo
WHERE element_subnational_id = est_id;
END;

--Rank of E is better than C or D--
BEGIN
IF d_id < 14 AND d_id > 8 AND e_rank = 1 Then
d_id := 14;
ELSE
d_id := d_id;
END IF;
END;

--Use ID to retrieve EO Rank Code--
BEGIN
SELECT basic_eo_rank_cd INTO rank FROM d_basic_eo_rank WHERE d_basic_eo_rank_id = d_id;
END;

RETURN rank;

EXCEPTION
WHEN NO_DATA_FOUND Then
RETURN null;
WHEN OTHERS THEN
RETURN 'ERROR!';

END;
/