Subquery alternative for a function?-CodePudding

i'm trying to get a procedure that will get as parameters 2 varchars (1 for the first name, and 1 for the last name) and will return (if exists) the id of the manager with that name;

My employees table looks something like this:

EMPLOYEE_ID NUMBER,
FIRST_NAME VARCHAR(50),
LAST_NAME VARCHAR(50),
MANAGER_ID NUMBER

My function is right here:

CREATE OR REPLACE FUNCTION f1_test(v_fn employees.first_name%type,
                                    v_ln employees.last_name%type) RETURN NUMBER IS numar NUMBER;
    BEGIN
        SELECT e.employee_id INTO numar from
        employees e
        where e.last_name = v_ln and e.first_name = v_fn;
        IF numar IN (SELECT manager_id from employees)  /*here is the problem */
            THEN DBMS_OUTPUT.PUT_LINE('That is not a manager');
            ELSE return numar;
        END IF;
        return numar;
        end;
        /

When i run it i get this error PLS-00405: subquery not allowed in this context Is there any alternative for this subquery that verifies if the id of the employee is actually an id of a manager?

CodePudding user response：

You could do a second query, such as a count of matching rows, and test that result:

CREATE OR REPLACE FUNCTION f1_test(v_fn employees.first_name%type,
                                    v_ln employees.last_name%type)
RETURN NUMBER IS
    numar NUMBER;
    manager_count NUMBER;
BEGIN
    SELECT e.employee_id INTO numar from
    employees e
    where e.last_name = v_ln and e.first_name = v_fn;

    SELECT count(*) INTO manager_count from
    employees e
    where manager_id = numar;

    IF manager_count = 0
        THEN DBMS_OUTPUT.PUT_LINE('That is not a manager');
        ELSE return numar;
    END IF;
    return numar;
END;
/

Or within a single query you could use an outer join and count matching rows, or - I think a little more clearly, and potentially more efficiently (not really here, but in more complicated situation) test with exists:

CREATE OR REPLACE FUNCTION f1_test(v_fn employees.first_name%type,
                                    v_ln employees.last_name%type)
RETURN NUMBER IS
    numar NUMBER;
    is_manager VARCHAR2(3);
BEGIN
    SELECT e.employee_id,
        case when exists (
            select null from employees m where m.manager_id = e.employee_id
        ) then 'yes' else 'no' end
    INTO numar, is_manager from
    employees e
    where e.last_name = v_ln and e.first_name = v_fn;

    IF is_manager != 'yes'
        THEN DBMS_OUTPUT.PUT_LINE('That is not a manager');
        ELSE return numar;
    END IF;
    return numar;
END;
/

db<>fiddle demo

You should also decide how you want to handle the parameters you are passed not matching any rows. At the moment the query will throw "ORA-01403: no data found" in a PL/SQL context, or just return null in a SQL context, which may be fine.

It generally isn't safe to assume someone calling your function will be able to see anything you send through dbms_output (by default db<>fiddle doesn't show it for the SQL call, for example); and your else branch is a bit pointless since you return the numar value either way.

All of that is going rather out of scope of your actual question though.

CodePudding user response：

Building upon Alex Poole’s answer, an alternative may be the following.


CREATE OR REPLACE FUNCTION f1_test(v_fn employees.first_name%type,
                                    v_ln employees.last_name%type)
RETURN NUMBER IS
    numar NUMBER;
    manager_id employees.manager_id%type;
BEGIN
    with 
      "S" as
      ( select e.employee_id
             , m.manager_id
             , 1 srt
          from employees e
               left join employees m
                      on e.employee_id = m.employee_id
         where e.last_name      = v_ln
               and e.first_name = v_fn
               and rownum = 1
        union all
        select null
             , null
             , 2
          from dual
      )
      select employee_id
           , manager_id
        into numar
           , manager_id
        from "S"
       where rownum = 1
       order by srt
    ;
    
    if numar is null then
      dbms_output.put_line('That is not an employee');
    elsif manager_id is null then 
      DBMS_OUTPUT.PUT_LINE('That is not a manager');
    end if;
    
    return numar;
END;
/

Edit Or, perhaps a more elegant solution is this refactored version.

CREATE OR REPLACE FUNCTION f1_test(v_fn employees.first_name%type,
                                    v_ln employees.last_name%type)
RETURN NUMBER IS
    numar NUMBER;
    manager_id employees.manager_id%type;
BEGIN
    select e.employee_id
         , m.manager_id
      into numar
         , manager_id         
      from dual 
           left join employees e
                  on e.last_name      = v_ln
                     and e.first_name = v_fn
           left join employees m
                  on e.employee_id = m.employee_id
     where rownum = 1
    ;
    
    if numar is null then
      dbms_output.put_line('That is not an employee');
    elsif manager_id is null then 
      DBMS_OUTPUT.PUT_LINE('That is not a manager');
    end if;
    
    return numar;
END;
/

db<>fiddle demo