Here is what I have done in a similar situation. The VOLUMES tables
stores removable volumes, the PATH table stores each folder of the volumes.
The stored procedure receives the primary key of a path and a path
separator character, and it returns the full path, starting with the
volume name. It is recursive and it walks the paths chain until it finds
the root folder.
Probably there are better ways to do it, but this can be a starting point.

Fulvio Senore

ALTER PROCEDURE SP_GET_FULL_PATH (
PHYS_PATH_ID BIGINT,
PATH_SEPARATOR VARCHAR(5))
RETURNS (
FULL_PATH VARCHAR(500))
AS
declare variable TMP_ID bigint;
declare variable FATH_ID bigint;
declare variable TMP_VOL_NAME varchar(500);
declare variable VOL_NAME varchar(500);
declare variable TMP_PATH_NAME varchar(500);
declare variable PATH_NAME varchar(500);
begin
-- looks for the father ID and the volume name
for select PATHS.FATHER_ID, PATHS.PATH_NAME, VOLUMES.VOLUME_NAME
from PATHS inner join VOLUMES
on PATHS.VOLUME_ID = VOLUMES.VOLUME_ID
where PATHS.PATH_ID = :PHYS_PATH_ID
into
:TMP_ID, :TMP_PATH_NAME, :TMP_VOL_NAME
do
begin
FATH_ID = TMP_ID;
VOL_NAME = TMP_VOL_NAME;
PATH_NAME = TMP_PATH_NAME;
end

if( FATH_ID is null ) then
begin
-- this is the root path, return the volume name
FULL_PATH = VOL_NAME;
end
else
begin
-- recursion into the father folder
execute procedure SP_GET_FULL_PATH( :FATH_ID, :PATH_SEPARATOR )
returning_values( :TMP_PATH_NAME );
FULL_PATH = TMP_PATH_NAME || PATH_SEPARATOR || PATH_NAME;
end
end



James N Hitz ha scritto:

> Supposing I had a list of Departments in a table like this:
>
> ID | DEPARTMENT | PARENTID
> 1 | INFO. TECH. | NULL
> 2 | ACCOUNTS | NULL
> 3 | SOFTWARE DEVLPT | 1
> 4 | REQUISITIONS | 2
> 14 | QUALITY ASSURANCE | 3
>
> What would be the best way to produce output showing the entire path for
> every "sub-department" (WHERE PARENTID NOT IS NULL ORDER BY 2) to get
> something like this:
>
> ID | DEPT_PATH
> 4 | ACCOUNTS - REQUISITIONS
> 3 | INFO. TECH. - SOFTWARE DEVLPT
> 14 | INFO. TECH. - SOFTWARE DEVLPT - QUALITY ASSURANCE
>
> I think a recursive Stored Proc. Any suggestions?
>
> Thanks
> James
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>