## Overcoming "many-to-many" cartesian joins via "inline" stored functions

    Select E.emp_no, E.emp_name,
           sum(S.days) sick_days, 
           sum(H.days) holidays
    From   holiday_leave H,
           sick_leave S,
           emp E
    Where  E.emp_no = :emp_no
    And    E.emp_no = S.emp_no (+)
    And    E.emp_no = H.emp_no (+)
    Group  
        By E.emp_no, E.emp_name


## The "summed" columns are wrong. Rather than coding this SQL as 
## multiple SQL statements, we can use an in-line SQL functions.

    FUNCTION Sum_Sick_Leave (emp  IN number) return number 
    AS
      tot_days  number := 0;
      CURSOR C1 IS
      SELECT sum(days) 
      FROM   sick_leave
      WHERE  emp_no = emp;
    BEGIN
      Open  C1;
      Fetch C1 into tot_days;
      Close C1;
      return (tot_days);
    END;

    FUNCTION Sum_Holiday_Leave (emp IN number) return number
    AS
      tot_days  number := 0;
      CURSOR C1 IS
      SELECT sum(days) 
      FROM   holiday_leave
      WHERE  emp_no = emp;
    BEGIN
      Open  C1;
      Fetch C1 into tot_days;
      Close C1;
      return (tot_days);
    END;


    SELECT E.emp_no, E.emp_name,
           sum_sick_leave(E.emp_no) sick_days, 
           sum_holiday_leave(E.emp_no) holidays
    FROM   emp E
    WHERE  E.emp_no = :emp_no