Ejemplo de recursión (7). Números faltantes en una serie

1 comentario

Si en una tabla o en una vista o en un procedimiento almacenado seleccionable, tenemos una columna numérica y queremos saber si están todos los números o si falta alguno, podemos usar la técnica mostrada en este artículo para averiguarlo.

Para ello, mediante recursión crearemos una tabla virtual, que en nuestro ejemplo llamaremos RANGO_NUMEROS. Esa tabla virtual contendrá todos los números que nos interesan, de forma consecutiva. Es decir: 1, 2, 3, 4, 5, 6, 7, 8, 9, …

Desde luego que podemos empezar con cualquier número, no es obligatorio que empecemos con el número 1.

La tabla es virtual porque solamente existe en la memoria de la computadora y mientras dure la ejecución del SELECT principal, no existe dentro de la Base de Datos ni tampoco se guarda en el disco duro, solamente existe hasta que el SELECT principal finaliza, luego … desaparece totalmente.

Listado 1. Un SELECT para averiguar si hay números consecutivos faltantes

WITH RECURSIVE RANGO_NUMEROS AS (

   SELECT
      1 AS NUMERO
   FROM
      RDB$DATABASE

   UNION ALL

   SELECT
      NUMERO + 1 AS NUMERO
   FROM
      RANGO_NUMEROS
   WHERE
      NUMERO <= 36
)

SELECT
   NUMERO
FROM
   RANGO_NUMEROS
LEFT JOIN
   REVALUOSCAB
      ON NUMERO = RVC_IDENTI
WHERE
   RVC_IDENTI IS NULL

Como siempre que usamos recursión, debemos asignar el valor inicial (en nuestro ejemplo, es el número 1, pero puedes elegir otro número si quieres) y un valor final (en nuestro ejemplo, es 36).

El valor final es absolutamente necesario establecerlo porque de lo contrario la recursión continuaría indefinidamente. Bueno, en realidad, hasta que llegues al límite de recursiones permitidas o hasta que la computadora se quede sin memoria RAM.

Siempre que uses recursión debes establecer una condición de salida, es decir, una condición para que la recursión finalice. No tendría sentido de otro modo.

¿Que hicimos en el Listado 1.?

Primero, hemos creado una tabla virtual llamada RANGO_NUMEROS, cuyo contenido es una sola columna, llamada NUMERO, y cuyos valores van desde el 1 hasta el 37 de forma consecutiva, es decir sin que falte algún número. Están todos. Va hasta el 37 porque en el SELECT pusimos NUMERO + 1. Y como en el WHERE pusimos 36, entonces obtendremos un número más, en este caso 37.

Segundo, hemos hecho un LEFT JOIN de nuestra tabla virtual llamada RANGO_NUMEROS con la tabla REVALUOSCAB, la cual tiene los números que queremos verificar.

Tercero, pusimos la condición RVC_IDENTI IS NULL para que solamente nos muestre los números que están en la tabla virtual RANGO_NUMEROS y que no están en la tabla REVALUOSCAB. De esta manera, solamente los números que se encuentren en la tabla virtual RANGO_NUMEROS y que no se encuentren en la tabla REVALUOSCAB obtendremos en el resultado.

Captura 1. Si haces clic en la imagen la verás más grande

En la Captura 1. vemos el contenido de la columna RVC_IDENTI de la tabla REVALUOSCAB. Como puedes notar, faltan los números que van del 26 al 36.

Captura 2. Si haces clic en la imagen la verás más grande

Después de ejecutar el Listado 1. obtenemos como resultado lo que vemos en la Captura 2., es decir, todos los números faltantes.

Conclusión:

Hay varias técnicas para mostrar los números que faltan en una serie, en este artículo hemos visto una de esas técnicas, la cual emplea recursión. Saber usar recursión puede ayudarte en muchos casos, por lo tanto es muy bueno conocer como usarla.

Artículos relacionados:

Stored procedures recursivos

Entendiendo a las tablas autoreferenciadas

Usando CTE (Common Table Expression)

Otro ejemplo de CTE: ventas semanales

Usando varias CTE en una vista o en un stored procedure

FOR SELECT y tablas CTE

Usando recursividad con CTE

Ejemplo de recursión (1). Filas salteadas

Ejemplo de recursión (2). Numerar filas

Ejemplo de recursión (3). Fechas consecutivas

Ejemplo de recursión (4). Actualizando filas recursivamente

Ejemplo de recursión (5). Saldos acumulados

Ejemplo de recursión (6). Repitiendo las filas

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

Verificando si un string es un número válido

2 comentarios

A veces podemos necesitar saber si en una columna de tipo CHAR o VARCHAR se ha guardado un número válido.

¿Cómo lo conseguimos rápidamente?

Usando el predicado de comparación SIMILAR TO.

Ejemplo 1:

En la tabla de ALUMNOS tenemos una columna donde guardamos el número de la matrícula. Queremos verificar que solamente haya números válidos allí.

NUMEROS1

Captura 1. Si haces clic en la imagen la verás más grande

Listado 1.

SELECT
   ALU_MATRIC,
   ALU_NOMBRE
FROM
   ALUMNOS
WHERE
   ALU_MATRIC SIMILAR TO '[0-9]*'

NUMEROS2

Captura 2. Si haces clic en la imagen la verás más grande

Ejemplo 2:

Queremos saber cuales son los alumnos cuyo número de matrícula es incorrecto. Para ello solamente agregamos NOT, como podemos ver en el Listado 2.

Listado 2.

SELECT
   ALU_MATRIC,
   ALU_NOMBRE
FROM
   ALUMNOS
WHERE
   ALU_MATRIC NOT SIMILAR TO '[0-9]*'

NUMEROS3

Captura 3. Si haces clic en la imagen la verás más grande

Si lo que nos interesa es saber cuantos alumnos tienen número de matrícula correcto o número de matrícula incorrecto entonces en lugar de seleccionar la Matrícula y el Nombre usaríamos la función agregada COUNT().

Ejemplo 3:

En la tabla FACTURAS tenemos datos de las Facturas de venta.

NUMEROS4

Captura 4. Si haces clic en la imagen la verás más grande

Por alguna razón que no sabemos, la columna FAC_COBRAD es de  tipo VARCHAR, no es numérica. Por eso ahora queremos verificar que todos los números allí escritos sean números válidos.

Listado 3.

SELECT
   FAC_NUMERO,
   FAC_FECVEN,
   FAC_COBRAD
FROM
   FACTURAS
WHERE
   TRIM(FAC_COBRAD) SIMILAR TO '[\+\-]?[0-9]*.?[0-9]*' ESCAPE '\'

NUMEROS5

Captura 5. Si haces clic en la imagen la verás más grande

Explicando el patrón usado en SIMILAR TO

NUMEROS6

Conclusión:

Saber si en una columna de tipo CHAR o VARCHAR hay solamente números válidos puede ser muy útil muchas veces, en este artículo se mostró una técnica para realizar esa verificación.

Artículos relacionados:

Los predicados de comparación

Usando SIMILAR TO

El índice del blog Firebird21

El foro del blog Firebird21

 

Generando códigos dependientes

3 comentarios

A veces podemos encontrarnos con la siguiente situación: debemos codificar a los ítems pero esos códigos deben empezar con algunos caracteres predeterminados.

O sea, podríamos tener esta clase de códigos: AAC00001, AAC00002, AAC00003, CAM00001, CAM00002, ESC00001, ESC00002, ESC00003, etc.

Como ves, algunos códigos empiezan con AAC, algunos con CAM, algunos con ESC, etc. Y en cada caso la numeración empieza con 00001.

Una solución es la siguiente:

  1. Creamos una tabla de categorías, donde guardaremos las iniciales de los códigos (AAC, CAM, ESC, etc.). Esa columna debe tener la restricción Unique Key para que no tengamos códigos repetidos
  2. Creamos una tabla de bienes, donde se guardarán los códigos dependientes (AAC00001, AAC00002, AAC00003, etc.)
  3. A la columna donde guardaremos el código del bien le agregamos la restricción Unique Key
  4. En la tabla de bienes, creamos un trigger que se encargará de generar el código dependiente

CODIGO1

(haciendo clic en la imagen la verás más grande)

En la imagen de arriba podemos ver la estructura de la tabla CATEGORIAS. La columna CAT_INICIA debe tener la restricción Unique Key para asegurarnos que no tengamos iniciales repetidas.

CODIGO2

(haciendo clic en la imagen la verás más grande)

 En la imagen de arriba podemos ver algunas filas de la tabla CATEGORIAS.

CODIGO3

(haciendo clic en la imagen la verás más grande)

En la imagen de arriba podemos ver la estructura de la tabla BIENES. Recuerda que la columna BIE_CODIGO debe estar declararada como Unique Key para que el Firebird nos impida tener dos códigos idénticos.

A continuación, creamos un trigger como el siguiente:

SET TERM ^ ;

CREATE TRIGGER BIENES_BI FOR BIENES
   ACTIVE BEFORE INSERT
   POSITION 1
AS
   DECLARE VARIABLE lcCodigo D_CHAR8;
   DECLARE VARIABLE lnNumero D_CANTIDAD;
   DECLARE VARIABLE lcInicia D_CHAR3;
BEGIN

   /* Primero, se halla el último código de esta categoría */
   FOR SELECT
      BIE_CODIGO
   FROM
      BIENES
   WHERE
      BIE_IDECAT = NEW.BIE_IDECAT
   ORDER BY
      BIE_CODIGO
   INTO
      :lcCodigo
   DO BEGIN
   END

   /* Si no se encontró, el número será 1. Si se encontró, será el siguiente */
   IF (lcCodigo IS NULL) THEN
      lnNumero = 1;
   ELSE
      lnNumero = CAST(RIGHT(lcCodigo, 5) AS INTEGER) + 1;

   /* Se obtienen los caracteres iniciales del código */
   lcInicia = (SELECT CAT_INICIA FROM CATEGORIAS WHERE CAT_IDENTI = NEW.BIE_IDECAT);

   /* Se forma el código del bien que se grabará en la tabla */
   NEW.BIE_CODIGO = lcInicia || LPAD(lnNumero, 5, '0');

END^

SET TERM ; ^

Luego de insertar algunas filas en la tabla de BIENES esto es lo que obtenemos:

CODIGO4

(haciendo clic en la imagen la verás más grande)

Los usuarios solamente introdujeron el Identificador de la Categoría y el Nombre del Bien. El Identificador del Bien y el Código del Bien (la columna BIE_CODIGO) fueron puestos automáticamente por los triggers.

Observaciones:

  • En este ejemplo los códigos de los bienes empiezan con 3 caracteres predeterminados, pero pueden ser 2, 4, 5, ó cualquier otra cantidad. Eso se determina en la columna CAT_INICIA de la tabla CATEGORIAS
  • En este ejemplo los números tienen 5 dígitos y por lo tanto pueden variar entre 00001 y 99999 pero se pueden usar 2 dígitos, 3 dígitos, 7 dígitos o cualquier otra cantidad. Eso se determina en la columna BIE_CODIGO de la tabla BIENES. Como en este ejemplo la columna CAT_INICIA tiene 3 caracteres y el número máximo que se quiere usar es 99999 entonces la columna BIE_CODIGO tiene 8 caracteres.
  • Si más de una persona están introduciendo bienes al mismo tiempo entonces puede ocurrir que generen el mismo código del bien. Para evitar que se  graben códigos duplicados es que se debe usar la restricción Unique Key en la columna BIE_CODIGO. La primera persona grabará sin problemas, pero la segunda no podrá grabar porque el código ya existe y deberá volver a presionar el botón “Grabar” para que se genere un nuevo código del bien y allí sí se grabarán sus datos (bueno, si tiene mala suerte otra persona ya generó ese mismo código y deberá presionar el botón “Grabar” nuevamente hasta que tenga éxito. Si esta es una situación frecuente lo más conveniente es que sea el programa el encargado de reintentar la grabación hasta conseguirla).

Artículos relacionados:

https://firebird21.wordpress.com/2013/03/29/cast/

https://firebird21.wordpress.com/2013/04/10/lpad/

https://firebird21.wordpress.com/2013/04/14/right/

https://firebird21.wordpress.com/2013/03/17/entendiendo-a-los-triggers/

TRUNC()

1 comentario

Descripción: Devuelve la parte entera de un número. Con el argumento opcional escala, el número puede ser truncado a múltiplos de potencias de diez (decenas, centenas, etc., en lugar de solamente a enteros).

Tipo de resultado: Integer, (escalado) Bigint o Doble precisión

Sintaxis:

TRUNC(<número> [, escala])

número = cualquier expresión numérica

escala = un entero especificando la cantidad de lugares decimales que deben ser truncados, por ejemplo:

2 para truncar a un múltiplo de 0.01

1 para truncar a un múltiplo de 0.1

0 para truncar a un número entero

-1 para truncar a un múltiplo de 10

-2 para truncar a un múltiplo de 100

Notas:

  • Si la escala es especificada, el resultado generalmente tiene la misma escala que el primer argumento, ejemplos:
    • TRUNC(789.2225, 2) devolverá 789.2200 (no 789.22)
    • TRUNC(345.4, -2) devolverá 300.0 (no 300)
    • TRUNC(-163.41, 0) devolverá -163.00 (no -163)
  • Si no se especifica la escala entonces se considera que es cero, ejemplo:
    • TRUNC(-163.41) devolverá -163