Procedimiento almacenado para obtener Rubros y subrubros
 

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

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.

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

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.

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

Guillermo

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

Aqui tengo una nueva opcion, pero tienes que seguir algunas instrucciones:

1. Que los niveles no superen a los 15. Porque en el procedimiento GET_ARBOLRUBROS se agrega dos espacios en blanco por c/nivel.
2. Que la estructura este en el orden adecuado para que pueda funcionar.
3. En el IBDataSet llamar al procedimiento GET_ARBOLRUBROS asi:
SELECT NOMBRE FROM GET_ARBOLRUBROS(0).



/*Este procedimiento devuelve el Nª de nivel del registro.*/
CREATE PROCEDURE GET_NIVEL(ID INTEGER)
RETURNS(NIVEL INTEGER)
AS
DECLARE VARIABLE OID INTEGER;
DECLARE VARIABLE PADRE INTEGER;
BEGIN
NIVEL:=0;
OID:=:ID;
WHILE (:OID > 0) DO
BEGIN
SELECT ID_PADRE FROM RUBROS WHERE ID=:OID
INTO :PADRE;

OID:=:PADRE;
NIVEL:=:NIVEL+1;
END
SUSPEND;
END


/*Ten en cuenta el tamaño de tu campo NOMBRE*/
CREATE PROCEDURE GET_ARBOLRUBROS(ID_PADRE INTEGER)
RETURNS(NOMBRE VARCHAR(80))
AS
DECLARE VARIABLE ID INTEGER;
DECLARE VARIABLE NOMB VARCHAR(50);
DECLARE VARIABLE NIVEL INTEGER;
BEGIN
FOR SELECT ID, NOMBRE FROM RUBROS WHERE ID_PADRE=:ID_PADRE
ORDER BY ID INTO :ID, :NOMB DO
BEGIN
NOMBRE:='';

EXECUTE PROCEDURE GET_NIVEL(:ID)
RETURNING_VALUES(:NIVEL);

WHILE (:NIVEL > 1) DO
BEGIN
NOMBRE:='__' || :NOMBRE; /*'__'=espacios en blanco*/
NIVEL:=:NIVEL - 1;
END
NOMBRE:=:NOMBRE || :NOMB;
SUSPEND;

/*AQUI SE USA LA RECURSIVIDAD PARA OBTENER LOS HIJOS.*/
NOMBRE:='';
FOR SELECT NOMBRE FROM GET_ARBOLRUBROS(:ID)
INTO :NOMBRE DO
BEGIN
SUSPEND;
END
END
END


Ojala te sirva, saludos.

Hola.

A quienes han participado en este hilo, les recomiendo el uso de las etiquetas [code ], [delphi ] y [sql ] para publicar trozos de código, ya que sin estas, se hace realmente dificil leer el código.

Dado que existen, se me hace un gran desperdicio no usarlas.

Código SQL [-]
Select *
  from usuarios
 where usanetiquetas = 1

Código Delphi [-]
Begin
  if UsoLasEtiquetas Then
  begin
    ObtengoCodigoIndentado;
    YTambenResaltado;
  end;
  if not MessageDlg('Notas la diferencia?', mtConfirmation, [mbYes, mbNo], 0) <> mrYes Then
  Begin
    QuemateEnElInfierno;
    ShowMessage(':D');
  end;
end;

Hasta luego.

;)