Hi Raigo!

How can you expect this to work when you have only a for select loop?
I'm no SP expert, but you either want a recursive procedure (a
procedure that calls itself) or a loop that changes the where bit of
the select. The latter is probably simpler to understand and less
error prone.

Try something like

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

BEGIN

dept_nr = :dept_no;

while (:dept_nr IS not NULL)
BEGIN

SELECT head_dept, department
FROM department
WHERE dept_no = :dept_nr
INTO :dept_nr, :DEPARTMENT;

SUSPEND;

END

You have to do some adjustments if a department can have more than one
head department.

HTH,
Set

--- In firebird-support@yahoogroups.com, Raigo wrote:
> 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
>
> >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.