Procedimiento almacenado para obtener Rubros y subrubros

[oliverinf]

Hola a todos, estoy usando Firebird, en donde tengo la tabla Rubros(Id_Rubro, Nombre, Id_Padre), Id_Padre indica cual es el padre de un rubro. Está hecha así porque necesito mostrar jerárquimente los rubros, algo como,
R1
R11
R111
R12
R2
R21

Necesito imprimir esta estructura de árbol en un reporte y lo que pensé es hacer un procedimiento almacenado que me retorne un conjunto de registros en los cuales quiero colocar unos espacios en blanco para lograr la identación. Es decir, quiero obtener estos registros
R1
bbR11
bbbbR111
bbR12
R2
bbR21
Donde b es un espacio en blanco.

Lo que he hecho es lo siguiente pero no logro obtener lo que deseo
CREATE PROCEDURE GET_ARBOLRUBROS (
ID_R INTEGER)
RETURNS (
NOMB CHAR (50))
AS
declare variable Id_Rub Integer;
declare variable CantHijos Integer;
BEGIN
/* Para todos los rubros hijos de Id_R. */
for select "Id_Rubro", "Nombre" from "Rubros"
where ("Id_Padre" = :Id_R)
order by "Nombre"
into :Id_Rub, :Nomb
do begin
suspend;
select count(*) from "Rubros"
where ("Id_Padre" = :Id_Rub)
into :CantHijos;
if (CantHijos = 0) then
suspend;
else
begin
execute procedure get_ArbolRubros(Id_Rub) returning_values :Nomb;
suspend;
end
end
END

Esta no es la versión final de lo que deseo, pero primero estoy tratando de obtener los registros, después le agregaré los espacios en blanco al principio.

Espero que alguien me pueda ayudar a resolverlo.

Desde ya mucha gracias

Guillermo

[JulioGO]

Porque no intentas esto:

ID NOMBRE ID_PADRE
1 RUBRO1 0
2 RUBRO11 1
3 RUBRO2 0
4 RUBRO21 3

///***LOS PADRES TIENEN QUE TENER COMO ID_PADRE=0
//***
CREATE PROCEDURE GET_ARBOLRUBROS
RETURNS (NOMB_PADRE CHAR (50), NOMB_HIJO VARCAR(50))
AS
BEGIN
FOR SELECT NOMBRE FROM RUBROS
WHERE ID_PADRE=0 ORDER BY ID INTO :NOMB_PADRE DO
BEGIN
FOR SELECT NOMBRE FROM RUBROS
WHERE ID>0 ORDER BY ID INTO :NOMB_HIJO DO
BEGIN
SUSPEND;
END
END
END

Armas tu reporte y lo agrupas por el campo NOMB_PADRE.

Ojala te sirva.

[oliverinf]

Gracias por tu pronta respuesta.
Un detalle que me olvidé, es que pueden ser "infinitos" anidamientos de rubros.
Lo que me dices sólo serviría para dos niveles de anidamiento.
Yo creo que con un procedimiento recursivo funcionaría, pero no logro obtener todas las "ramas" del árbol.

Guillermo

[JulioGO]

Modifica tu tabla agregando un campo contad(este servira cuantos id hijos tiene)

ID NOMBRE ID_PADRE CONTAD
1 RUBRO1 0 1
2 RUBRO11 1 1
3 RUBRO111 2 0 ///FIJATE EN ESTE DETALLE
3 RUBRO2 0
4 RUBRO21 3

Aqui te mando este procedure para rectificar mi error:

CREATE PROCEDURE OBTENER_HIJOS(ID_PADRE INTEGER)
RETURNS(NOMBRE VARCHAR(50))
AS
BEGIN
FOR SELECT NOMBRE FROM RUBROS
WHERE ID_PADRE=:ID_PADRE ORDER BY ID INTO :NOMBRE DO
BEGIN
SUSPEND;
END
END

Ahora modifica

CREATE PROCEDURE GET_ARBOLRUBROS
RETURNS (NOMB_PADRE CHAR (50), NOMB_HIJO VARCAR(50))
AS
DECLARE VARIABLE ID INTEGER;
DECLARE VARIABLE CONT INTEGER;
BEGIN
FOR SELECT ID, NOMBRE, CONTAD FROM RUBROS
ORDER BY ID INTO :ID, :NOMB_PADRE, :CONTAD DO
BEGIN
IF (:CONTAD > 0) THEN
BEGIN
FOR SELECT NOMBRE FROM OBTENER_HIJOS(:ID)
INTO :NOMB_HIJO DO
BEGIN
SUSPEND;
END
END
END
END


Ojala te sirva, salu2.

[oliverinf]

Anduvo bárbaro!!!
Aunque no es lo que quiero hacer, me sirve para salir del paso.
Muchas gracias.

Guillermo

[oliverinf]

Con el siguiente procedimiento almacenado logre hacer lo que quería

CREATE PROCEDURE GET_ARBOLRUBROS (
ID_R INTEGER,
NIVEL INTEGER)
RETURNS (
NOMBRE VARCHAR (200))
AS
declare variable Id_Rub Integer;
declare variable CantHijos Integer;
declare variable i Integer;
declare variable NivelNuevo Integer;
declare variable Espacios VarChar(200);
BEGIN
/* Para todos los rubros hijos de Id_R. */
for select "Id_Rubro", "Nombre" from "Rubros"
where ("Id_Padre" = :Id_R)
order by "Nombre"
into :Id_Rub, :Nombre
do begin
/* Agrego tanto espacios en blanco como indica Nivel. */
Espacios = '';
i = 1;
while (i <= Nivel) do begin
Espacios = Espacios || ' ';
i = i + 1;
end

if (not (Espacios is null)) then
begin
/* Retorno los espacios concatenados con el nombre del rubro. */
Nombre = Espacios || Nombre;
end

/* Con este suspend retorno el Nombre del rubro padre. */
suspend;
/* Obtengo la cantidad de hijos del rubro padre. */
select count(*) from "Rubros"
where ("Id_Padre" = :Id_Rub)
into :CantHijos;

/* Si tiene hijos, volver a llamar al procedimiento. */
if (:CantHijos > 0) then
begin
NivelNuevo = Nivel + 1;
for select Nombre from get_arbolrubros(:Id_Rub, :NivelNuevo)
into :Nombre
do begin
/* Con este suspend retorno el Nombre del rubro hijo. */
suspend;
end
end
end

END