[ Number of Supervisor for each employee ]

[Dr. Oracle]

Dear all [:)] ,

Plz , I want to ask about what is SQL statments for extract report from Application database : <span id='hl' style='background-color: yellow'><b>Number of Supervisor for each employee .</b></span id='hl'>

Ex :

A > B > C :

A : 3 Supervisors

D > E > F > G :

D : 4 Supervisors

[Dr. Oracle]

plz zz zzzz ):

Is there funcation recive one parameter - persone id - and return number of supervisors for this one ?

[Dr. Oracle]

I have tried to find a best solution but I've written a sample funcation

and I wanted to share with you by :

FUNCTION XX_GET_SUPERVISOR_LEVELS(P_PERSON_ID number) RETURN NUMBER IS

c number := -1;

super_id number ;

p number := P_PERSON_ID;

BEGIN

while p is not null LOOP

select paaf.SUPERVISOR_ID INTO super_id

from per_all_assignments_f paaf

where paaf.PERSON_ID = p

and sysdate between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE;

p := super_id;

c := c+ 1;

END LOOP;

RETURN c;

EXCEPTION

WHEN NO_DATA_FOUND THEN RETURN 0;

END;

Thank u all for help me [V] [:(!] [V]