Crear secuencia numerica con un procedimiento almacenado

DobleSiete · #1 22-03-2005

Saludos a todos...

Estoy realizando un procedimiento almacenado que debe retornar una secuencia de numeros, esten o no esten en una tabla. La siguente tabla tiene un campo de codigos de cuentas contables (COD_INF), fijense que hay codigos que están salteados (1104 brinca a 1500):

Código:

  COD_INF	ESTADO    DESCRI_1		    			
  =======	======	======================================
  1100	   0		 ACTIVO FIJOS
  1101	   1		 CAJA Y BANCOS
  1102	   1    	 EFECTOS Y CUENTAS POR COBRAR    
  1103	   2    	 OTRAS CUENTAS POR COBRAR    			
  1104	   1		 INVENTARIO INICIAL
  1500	   0		 ACTIVO FIJO	  
  2102	   2		 CONTRIBUCIONES POR PAGAR
  2103	   2		 OTRAS CUENTAS POR PAGAR
  2105	   2		 PRESTACIONES SOCIALES
  3100	   3		 PATRIMONIO
  3101	   3		 PATRIMONIO 
  4100	   0		 INGRESOS
  4101	   4		 INGRESO POR VENTAS
  4102	   4		 OTROS INGRESOS
  4200	   5		 DESCUENTOS Y PROMOCIONES
  4201	   5		 DESCUENTOS Y DEVOLUCIONES
  4202	   5		 PROMOCIONES Y BONIFICACIONES
  5100	   5		 COSTO DE VENTAS
  5101	   5		 COSTO DE VENTAS
  6100	   5		 GASTOS DE OPERACION
  6120	   5		 GASTOS DE PERSONAL
  6130	   5		 GASTOS DEL LOCAL
  6140	   5		 GASTOS DE VEHICULOS
  6150	   5    	 GASTOS DE ADMINISTRACION    			 
  6160	   5		 OTROS GASTOS

... lo que quiero es que esos codigo aparezcan en el resultado, obviamente con los campos restantes en blanco (pues esos códigos no exsten en realidad). El procedimiento almacenado ya lo cree, y funciona... excepto por un pequeño detalle: si el resultado de la consulta con devuelve valores el contador tampoco tiene efecto. He aquí el procedimiento almacenado del que hablo:

Código SQL [-]  set term !!;
  create procedure rellenar
  returns (cod_inf  char(4),
           estado   char(1),
           descri_1 char(40))
  as
  declare variable cont int;
  begin
      cont = 1000;
      while (cont <= 7000) do
      begin
          for
              select cod_inf, estado, descri_1
              from scginf
              where estado = 6 /* si quito esta linea obtengo una secuencia */
              order by cod_inf
              into :cod_inf, :estado, :descri_1
          do
          begin
              if (:cod_inf <> cont) then
              begin
                  cod_inf = cont;
                  estado = '';
                  descri_1 = '';
              end
              suspend;
          end
          cont = cont + 1;
      end
  end!!
  set term ;!!

Y aquí la sentencia que la ejecuta:

Código SQL [-]  select * from rellenar

Noten que en el WHERE le indico que ESTADO debe ser igual a 6, ya que no hay registros con ese numero la consulta queda vacía, pero el asunto es que si cont es diferente a cod_inf (o sea, no existe ese codigo) debería sustituir el codigo contable con el contador, y mostrar una secuencia numerica del 1000 al 7000... eso funciona solamente si quito la clausula WHERE.

Cualquier comentario, critica (constructiva o destructiva), será bien recibida ...

PeLuCa · #2 05-04-2005

Hola que tal, si es que entendi bien, o por lo que veo en la tabla el estado 6 del que tu hablas no existe, por eso que ese select

select cod_inf, estado, descri_1
from scginf
where estado = 6 /* si quito esta linea obtengo una secuencia */
order by cod_inf
into :cod_inf, :estado, :descri_1

no obtendrias resultado alguno, creo que deberias hacerlo sin tener en cuenta el estado, es decir, quitar esa linea que dices.
Podrias optar por hacer un cursor ordenado por COD_INF y recorrerlo verificando el numero actual con el anterior, si es que son correlativos no haces nada, en caso contrario, ves la diferencia entre ellos para saber cuanto numeros agregar a la secuencia. Me parece mas general el procedemiento de esta forma.

Saludos.

DobleSiete · #3 13-04-2005

Cita:

Empezado por PeLuCa

Hola que tal, si es que entendi bien, o por lo que veo en la tabla el estado 6 del que tu hablas no existe, por eso que ese select

select cod_inf, estado, descri_1
from scginf
where estado = 6 /* si quito esta linea obtengo una secuencia */
order by cod_inf
into :cod_inf, :estado, :descri_1

no obtendrias resultado alguno, creo que deberias hacerlo sin tener en cuenta el estado, es decir, quitar esa linea que dices.
Podrias optar por hacer un cursor ordenado por COD_INF y recorrerlo verificando el numero actual con el anterior, si es que son correlativos no haces nada, en caso contrario, ves la diferencia entre ellos para saber cuanto numeros agregar a la secuencia. Me parece mas general el procedemiento de esta forma.

Saludos.

Hola...

Nunca he usado cursores, tal vez me sirva en otro caso... pero el caso es que necesito obligatoriamente una clausula WHERE ... el asunto entonces es como generar la secuencia númerica si el SELECT devuelve un resultado NULL...

¿Tal vez con un WHEN...DO?
¿como podría hacerlo?

8-(

Gracias de todas formas.

jachguate · #4 13-04-2005

No se si valga en tu caso, pero en lugar de un procedimiento almacenado podrias tener una tabla que ya contenga la secuencia de números que te interesa, y luego enlazarla via outer join (por ejemplo en una vista)

Supongamos que creamos la tabla Secuencia y que la rellenamos con los números del 1000 al 7000.

Luego podriamos hacer:

Código SQL [-]select sec.Numero, s.estado, s.descri_1
  from secuencia sec
       left outer join scginf s on s.cod_inf = sec.Numero
 order by sec.Numero;

Al menos a mi esta solución me parece mas elegante...

Hasta luego.

DobleSiete · #5 14-04-2005

Supongo que funcionaría en el caso de la secuencia siempre fuese la misma.

El ejemplo que les coloqué es solo eso, un ejemplo, el caso real es cuatro veces más complejo. En ese caso, la secuencia es hasta 31 (los 31 días del mes)... de modo que tu idea funcionaría perfectamente (teoricamente, porque todavía no la he probado).

Pero me sigue intrigando algo: ¿como sería generar esa secuencia de 31 o 3000 numeros usando un contador, cuando el resultado es nulo?

Se supone que se puede atrapar con un WHEN SQLCODE 100 DO sin embargo he probado con esa setencia y no funciona... (esa es al razón por la que postee el mensaje).

jachguate · #6 14-04-2005

Hola.

Hay varias formas de conseguir el resultado desde un sp. Me he dado a la tarea de codificar una de ellas y en mis pruebas todo ha sido satisfactorio.

Aqui dejo el script para que lo probes por vos mismo y que lo adaptes a tus necesidades.

Código SQL [-]Create table tabla1 (
  contador  integer not null primary key,
  campo1 varchar(50),
  campo2 varchar(50)
);

insert into tabla1
values (10, 'diez', 'diez');

insert into tabla1
values (20, 'veinte', 'veinte');

Set term ^ ;

create procedure Tabla_Secuenciada as Begin exit; end^

set term ; ^

commit;

Set term ^ ;

Alter procedure Tabla_Secuenciada
(
  contador_ini integer,
  contador_fin integer
)
returns
(
  contador integer,
  Campo1 Varchar(50),
  Campo2 VarChar(50)
)
AS
  declare variable contador_temp integer;
  declare variable campo1_temp varchar(50);
  declare variable campo2_temp varchar(50);

Begin
/* retornar :contador, :campo1 y :campo2 */
  contador = contador_ini - 1;
  for Select contador, campo1, campo2
        from tabla1
       where contador between :contador_ini and :contador_fin
        into :contador_temp, :campo1_temp, :campo2_temp
  do
  Begin
    contador = contador + 1;
    if (contador_temp > contador) Then
    begin
      campo1 = null;
      campo2 = null;
      while (contador < contador_temp) do
      Begin
        suspend;
        contador = contador + 1;
      end
    end
    /* cuando llegamos a este punto, :contador siempre será igual a :contador_temp */
    contador = contador_temp;
    campo1 = campo1_temp;
    campo2 = campo2_temp;
    suspend;
  end
  /* al llegar a este punto es probable que no se halla alcanzado el contador final, rellenar */
  if (contador_fin > contador) Then
  begin
    campo1 = null;
    campo2 = null;
    while (contador < contador_fin) do
    Begin
      contador = contador + 1;
      suspend;
    end
  end
end
^

set term ; ^

commit;


select * from tabla_secuenciada(1, 5);
select * from tabla_secuenciada(10, 10);
select * from tabla_secuenciada(10, 20);
select * from tabla_secuenciada(5, 25);
select * from tabla_secuenciada(15, 20);
select * from tabla_secuenciada(15, 25);
select * from tabla_secuenciada(21, 25);
select * from tabla_secuenciada(25, 25);

Espero sea de ayuda para vos y para la comunidad.

Hasta luego.

DobleSiete · #7 15-04-2005

Hola Jachguate !!

...prove tu código y funcionó perfectamente !!! muchisimas gracias...

Ahora lo que no entiendo es porque mi humilde programita no solo no funciona, sino que ademas provoca un bloqueo que el IBConsole (tengo que terminar el proceso en el administrador de tareas de windows)... he aquí mi código:

Código SQL [-]   SET TERM !!;
   CREATE PROCEDURE proc_rellenar
   RETURNS (cod_inf  CHAR(4),
            estado   CHAR(1),
            descri_1 CHAR(40))
   AS
   DECLARE VARIABLE cont INT;
   BEGIN
       cont = 1000;
       WHILE (cont <= 7000) DO
       BEGIN
           FOR
               SELECT   cod_inf, estado, descri_1
               FROM     scginf
               WHERE    cod_inf = :cont
                        AND estado = 6
               ORDER BY cod_inf
               INTO     :cod_inf, :estado, :descri_1
           DO
           BEGIN
               SUSPEND;
               cont = cont + 1;
           END
       END
   
       /* cuando la consulta no retorne resultados */
   
       WHEN SQLCODE 100 DO
       BEGIN
           cont = 1000;
           WHILE (cont <= 7000) DO
           BEGIN
               cod_inf  = cont;
               estado   = '';
               descri_1 = '';
               cont = cont + 1;
               SUSPEND;
           END
       END
   END!!
   SET TERM ;!!

jachguate · #8 15-04-2005

Creo que no has comprendido la idea completamente y pues solo has complicado tu código... eso de comprender la idea ya te toca a vos, dado que aqui venimos para aprender a programar y afinar nuestra lógica, mas que para encontrar quien programe por nosotros...

Realmente no es tan dificil la tarea de adaptar el código de mi sp para que funcione bajo tus condiciones... pero no te vas a ir de aqui sin ensuciarte las manos, asi que ¡a picar piedra!.

Tratá de comprender la lógica del sp, y luego bastará con sustituir un par de cosas para que funcione en tu caso.

Hasta luego.

DobleSiete · #9 15-04-2005

Cita:

Empezado por jachguate

Creo que no has comprendido la idea completamente y pues solo has complicado tu código... eso de comprender la idea ya te toca a vos, dado que aqui venimos para aprender a programar y afinar nuestra lógica, mas que para encontrar quien programe por nosotros...

Pero claro que entendí la idea... es mas, resulta mas practico que lo que estaba haciendo, por que con tu ejemplo puedo cambiar el intervalo como yo quiera (no solo con los 31 días del mes)....

Lo que quise decir, es que no entiendo el fallo que provoca mi programa (si es que no solo no funciona, sino que ademas deja colgado IBconsole!!!)

Esa es mi confusión. Postee el programa para ver si podias hacerle un diagnostico y decirme donde meti la pata para evitar el error en el futuro.

Fijate que usa WHEN...DO, por lo tanto se supone que es mas practico, pues captura un resultado nulo de una consulta...

Gracias otra vez por el programa, ya me ocupare de sacarle el jugo 8-)

jachguate · #10 15-04-2005

Cita:

Empezado por DobleSiete

Pero claro que entendí la idea...

Cita:

Empezado por DobleSiete

no entiendo el fallo que provoca mi programa (si es que no solo no funciona, sino que ademas deja colgado IBconsole!!!)

Creo que ya captaste la idea de lo que mi rutina hace, pero sigo creyendo que no has captado la idea de como lo hace.

Estoy seguro que IBConsole no se queda colgado... se queda esperando respuesta del servidor, que seguramente está atendiendo un ciclo infinito. No puedo decir mas pues ahora estoy corto de tiempo para analizarlo mas despacio.

Cita:

Empezado por DobleSiete

Fijate que usa WHEN...DO, por lo tanto se supone que es mas practico, pues captura un resultado nulo de una consulta...

No se exactamente lo que has querido decir con esto... con el planteamiento de mi procedimiento creo que no hace falta un when do y no entiendo en que puede resultar mas práctico usarlo.

Cita:

Empezado por DobleSiete

Gracias otra vez por el programa, ya me ocupare de sacarle el jugo 8-)

No hay por que... ya veremos si realmente te has preocupado por sacarselo...

Hasta luego.