title: Oracle query returning a manager that's not terminated PL/SQL procedure
body: <p>I'm currently re-writing a query and would like to improvise it.</p>
  
  <p>My website has the following structure:</p>
  
  <blockquote>
    <p>DEPARTMENT:</p>
    
    <p>Highest level attainable</p>
    
    <p>EMPLOYEE_VIEW VIEW:</p>
    
    <p>View containing all employee related information</p>
    
    <p>SERVICE AREA GROUP:</p>
    
    <p>Child of a department</p>
    
    <p>SERVICE AREA:</p>
    
    <p>Child of a service area group</p>
    
    <p>SERVICE_AREA TABLE:</p>
    
    <p>Table containing the service areas</p>
    
    <p>SERVICE_AREA_EMPLOYEE:</p>
    
    <p>Table containing employee relation to the service area table.</p>
  </blockquote>
  
  <p>Each department, service area group, and service area has one manager.
  The query currently returns active staff (STAFF) and managers (MGR) primary sequences.
  It does this by using a <strong>function</strong> that will query to see if the current employee sequence throws a <strong>NO_DATA_FOUND</strong> exception.</p>
  
  <p>Function called:</p>
  
  <pre><code>FUNCTION GETSERVICEAREAMGREMPSEQ(   ARG_EMP_SEQ NUMBER,
                                      ARG_POS_SEQ NUMBER,
                                      ARG_FLAG CHAR
                                  ) RETURN NUMBER
      IS
          INTOUTPUT NUMBER;
  BEGIN
  
  BEGIN
  SELECT DECODE(ARG_FLAG, 'E', ARG_EMP_SEQ, VS.POSITION_SEQ) INTO INTOUTPUT FROM SCHEMA.EMPLOYEE_VIEW VS WHERE VS.EMPLOYEE_SEQ = ARG_EMP_SEQ
  AND VS.TERMINATION_DATE IS NULL;
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
          BEGIN
  
              SELECT DECODE(ARG_FLAG, 'E', VS.EMPLOYEE_SEQ, ARG_POS_SEQ)  INTO INTOUTPUT FROM SCHEMA.EMPLOYEE_VIEW VS WHERE VS.POSITION_SEQ = ARG_POS_SEQ
              AND VS.TERMINATION_DATE IS NULL AND ROWNUM=1;
  
                  EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                      INTOUTPUT := -1;
  
          END;
  
       WHEN OTHERS THEN
          INTOUTPUT := -1;
  
  END;
  RETURN INTOUTPUT;
  END;
  </code></pre>
  
  <p>If the NO_DATA_FOUND exception also throws an exception then it won't match anyone for that service area.</p>
  
  <p>Can this be done in a single query without functions?</p>