CODEWITHSUNDEEP
sqloracle-databaseplsql
Coat
Coat

Reputation: 717

PL/SQL Find Item With Most Information

I've got a table that looks something like

CREATE_TABLE employees
{
    employee_id number(10),
    name varchar2(50),
    address varchar2(50)
};

And I want to find the nearest match to a search. I can run code like the following

CREATE FUNCTION FindBest (find_name int, find_address) return int IS
    id INT;

    BEGIN
        id := 0;

        BEGIN
            SELECT employee_id INTO id FROM employees WHERE name = find_name AND address = find_address;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                BEGIN
                    SELECT employee_id INTO id FROM employees WHERE name = find_name;
                    EXCEPTION
                        WHEN NO_DATA_FOUND THEN
                        BEGIN
                            SELECT employee_id INTO id FROM employees WHERE address = find_address;
                        END;
                END;
        END;

    return id;

END FindBest;

But is there some better way? Perhaps a query that looks like 

SELECT employee_id INTO id FROM employees WHERE name = find_name OR address = find_address ORDER BY {Best Match} LIMIT 1;

Upvotes: 0

Views: 105

Answers (1)

sstan
sstan

Reputation: 36473

You can apply the same logic using a single simplified query like this:

select *
  from (select *
          from employees
         where name = find_name
            or address = find_address
         order by case when name = find_name and address = find_address then 1
                       when name = find_name then 2
                       else 3
                  end)
 where rownum < 2;

Upvotes: 2

Related Questions