I tried following, but something is wrong.

CREATE PROCEDURE GET_departnemt_head(
dept_NO char(3))
RETURNS (
head_dept char(3))
AS
BEGIN
SELECT head_dept
FROM department
WHERE dept_NO = :dept_NO
INTO :head_dept;

SUSPEND;
END;


CREATE PROCEDURE dept_superiors(
dept_NO char(3))
RETURNS (
dept_nr char(3),
DEPARTMENT CHAR(25))
AS

BEGIN
SELECT head_dept
FROM department where dept_no=:dept_no
INTO :dept_nr;


IF (:dept_nr IS not NULL) THEN
BEGIN

for SELECT dept_no, deparment
FROM department
WHERE dept_no = (select head_dept from GET_departnemt_head(:dept_nr))
INTO :dept_nr, :DEPARTMENT;

END

SUSPEND;
END






Raigo wrote:

>In Emplyee database there is a table DEPARTMENT and a procedure
>ORG_CHART to demonstrate nested select.
>
>My question: with this sort of table, how is it possible to get all
>master DEPARTMENTS of certain department?
>
>For example to get hierarchy for department "marketing".
>180 Marketing, has head 100 Sales & Marketing, and it has top 000
>Headquarters.
>
>With input value dept_no 180, how to return something like this:
>180 Marketing
>100 Sales & Marketing
>000 Headquarters
>
>I understand I shoud create some loop to go until dept_no is null.
>
>thanks.
>
>
>
>