Consultas con columnas condicionales

2 comentarios

A veces en nuestras consultas necesitamos mostrar columnas cuyos contenidos provienen de otras tablas y además lo hacen en forma condicional.

Ejemplo:

En una tabla MOVIMCAB tenemos una columna llamada MVC_TIPMOV la cual nos indica el tipo de movimiento (‘ECM’ es una entrada por compras, ‘SVT’ es una salida por ventas, etc.) y donde también guardamos el Identificador del Proveedor (cuando se trata de una compra) o el Identificador del Cliente (cuando se trata de una venta).

Ahora, necesitamos una consulta que nos muestre el nombre del Proveedor o el nombre del Cliente, según corresponda. Para ello haremos uso de la construcción CASE … WHEN … END

SELECT
   MVC_FECHAX,
   MVC_NRODOC,
   MVC_TIPMOV,
   CASE
      WHEN MVC_TIPMOV = 'ECM' THEN (SELECT PRO_NOMBRE FROM PROVEEDORES WHERE PRO_IDENTI = MVC_IDEPRO)
      WHEN MVC_TIPMOV = 'SVT' THEN (SELECT CLI_NOMBRE FROM CLIENTES    WHERE CLI_IDENTI = MVC_IDECLI)
   END AS NOMBRE
FROM
   MOVIMCAB
WHERE
   MVC_TIPMOV IN ('ECM', 'SVT')

Y este es el resultado (parcial, por supuesto) que obtuvimos:

CONDICIONAL1

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

Como puedes ver, en la columna NOMBRE tenemos el nombre de un Proveedor (cuando se trata de una compra) o el nombre de un Cliente (cuando se trata de una venta). Y para saber de cual de ellos se trata podemos mirar el contenido de la columna MVC_TIPMOV.

ERROR: Multiple rows in singleton select

Si ves ese mensaje de error significa que el SELECT interno devolvió más de una fila pero debería haber devuelto solamente una fila (o ninguna, pero jamás más de una).

¿Cuál es el problema?

Que si el SELECT devuelve varias filas el Firebird no puede saber cual de esas filas es la correcta. Por ejemplo, si el SELECT devuelve el nombre de 500 clientes ¿cuál de esos 500 nombres es el que debe mostrar? imposible que el Firebird pueda saberlo y por lo tanto te muestra un mensaje de error.

Para evitar el error siempre tienes que asegurarte que la condición que pusiste en la cláusula WHERE limite el resultado a una sola fila.

En el ejemplo de arriba se usó el Identificador del Proveedor o del Cliente. Como esos identificadores son únicos y jamás pueden repetirse entonces estamos seguros de que jamás obtendremos el error “multiple rows in singleton select”. Sin embargo cuando no usas identificadores ni claves primarias ni claves únicas la posibilidad de encontrar ese error siempre existirá.

Artículo relacionado:

El índice del blog Firebird21

Usando transacciones con acceso exclusivo a tablas

5 comentarios

Hay algo que debes tener muy claro: en Firebird no se recomienda que una transacción tenga el acceso exclusivo a una o más tablas. Si normalmente necesitas tener acceso exclusivo a las tablas evidentemente aún no entiendes como funciona Firebird y deberías leer mucho sobre las transacciones y su manejo.

Sin embargo esta regla tiene algunas (poquísimas) excepciones. Una de esas excepciones es cuando necesitas numerar documentos en forma consecutiva y sin que falte algún número. O asignar códigos consecutivos y sin que existan códigos faltantes.

Para casos como los anteriores no puedes simplemente usar un generador (también llamado secuencia) porque si la transacción termina con un ROLLBACK el generador no regresa a su valor anterior. Recuerda que los generadores están afuera de las transacciones.

Ejemplo (usando un generador):

  • El último número de Factura guardado es el 12956
  • Se inicia una transacción, como usa un generador asigna el valor 12957 a la nueva Factura, pero por algún motivo la Factura no pudo ser grabada y la transacción termina con un ROLLBACK.
  • Se inicia otra transacción, la cual ahora asigna el valor 12958 a la nueva Factura. Esta transacción sí finalizó con un COMMIT

Luego cuando se ejecuta un SELECT se ve que la siguiente Factura después de la 12956 es la 12958. ¿Y la Factura 12957? ¿Qué pasó con ella? No está, no existe, no la encontramos ni buscándola con lupa.

El problema que ocurrió aquí fue causado por haber usado un generador para numerar a las Facturas. Y es que los generadores jamás retroceden en su valor, siempre van al siguiente, sin importar como haya terminado la transacción (con un COMMIT o con un ROLLBACK). Eso es así porque los generadores son independientes de las transacciones, están afuera de ellas.

Bueno, en realidad sí se puede alterar el valor de un generador pero no es aconsejable hacerlo porque es muy propenso a crear conflictos.

La solución: acceso exclusivo a una tabla

Para casos como los del ejemplo anterior es que Firebird nos da la posibilidad de acceder a las tablas en forma exclusiva. De esta manera encontrar la solución es muy fácil:

  1. En una tabla (llamada por ejemplo CONFIGURACION) tenemos guardado el último número de Factura
  2. Abrimos una transacción con la cláusula RESERVING y por lo tanto todas las tablas que se nombren después de la cláusula RESERVING se abrirán en forma exclusiva.
  3. Le sumamos 1 (uno) al último número de Factura que tenemos guardado en la tabla CONFIGURACION
  4. Utilizamos ese número para numerar a nuestra nueva Factura
  5. Ejecutamos los INSERTs correspondientes a nuestras tablas cabecera y detalles de Facturas
  6. Cerramos la transacción

Si la transacción finalizó exitosamente con un COMMIT, todo bien, siguiendo el ejemplo anterior ahora el último número de Factura guardado en la tabla CONFIGURACION será el 12957.

Si la transacción finalizó con un ROLLBACK tampoco hay problema, porque el último número de Factura volverá a su valor anterior de 12956.

O sea que sin importar como termine la transacción (con un COMMIT o con un ROLLBACK) en la tabla de CONFIGURACION siempre tendremos registrado el último número correcto de Factura.

¿Es necesario usar una tabla adicional?

Una pregunta que quizás te hayas hecho es:

¿Es necesario usar una tabla adicional (llamada CONFIGURACION en nuestro ejemplo), no podríamos simplemente buscar el último número de Factura guardado en nuestra tabla cabecera de Ventas y sumarle 1 (uno)?

Es necesario usar una tabla adicional. Buscar el último número de Factura guardado en la tabla cabecera de Ventas y sumarle (1) uno sería una muy mala idea. ¿Por qué? Porque si varios usuarios están guardando ventas podría ocurrir algo como:

  • El usuario Juan busca el último número de Factura, halla que es el 12956, le suma 1 y su Factura será la 12957
  • El usuario Ricardo busca el último número de Factura, halla que es el 12956, le suma 1 y su Factura será la 12957
  • El usuario Andrés busca el último número de Factura, halla que es el 12956, le suma 1 y su Factura será la 12957
  • El usuario Ricardo termina su transacción con un COMMIT, todo bien para él, ningún problema
  • El usuario Andrés quiere terminar su transacción con un COMMIT pero obtiene un error: “Número de Factura ya existe”
  • El usuario Juan quiere terminar su transacción con un COMMIT pero obtiene un error: “Número de Factura ya existe”

¿Se puede solucionar? Claro, como la transacción del usuario Juan encontró que el número de Factura ya existía entonces vuelve a leer el último número de Factura (que ahora será el 12957) y vuelve a sumarle 1 (uno). Si tiene suerte esta vez podrá finalizar con un COMMIT. Pero … ¿y si un milisegundo antes la transacción de Andrés hizo lo mismo? Pues la transacción de Juan no podrá finalizar con un COMMIT. Y tendrá que volver a leer el último número de Factura guardado en la tabla cabecera de Ventas (que ahora será el 12958), tendrá que volver a sumarle 1 (uno) y tendrá que volver a intentar el COMMIT … sólo para descubrir que ahora la transacción de Matías ya usó ese número. Y seguirá así hasta que por fin en algún momento pueda finalizar con su dichoso COMMIT.

Si son solamente dos o tres personas las que están registrando las ventas entonces es muy poco probable que te encuentres con esta clase de conflictos, pero ¿y son cientos o miles las personas que están insertando filas en la misma tabla y al mismo tiempo?

Pues allí sí que tendrás problemas mayúsculos. O le muestras un mensaje al usuario: “Estimado, no puedo grabar tu venta porque por un tonto error de mi parte permití que ese mismo número de Factura lo grabe otra persona” o haces que la transacción continúe intentando el COMMIT hasta lograrlo … alguna vez.

Entonces … ¿por qué cornos complicarte la vida si la solución es tan sencilla? Utilizas una tabla donde registras el último número de Factura y listo.

Es cierto que esa tabla puede también estar bloqueada por otra transacción pero en cuanto se desbloquee podrás obtener el número de Factura que corresponde a tu venta.

Abriendo la transacción:

Para abrir una transacción que te otorgue acceso exclusivo a una tabla para cambiar algún valor de esa tabla podrías escribir algo como:

SET TRANSACTION READ WRITE READ COMMITTED RESERVING CONFIGURACION FOR PROTECTED WRITE

Como puedes ver a continuación de la cláusula RESERVING se escribió el nombre de la tabla (CONFIGURACION, en este ejemplo) que se quiere reservar. Si se quiere reservar varias tablas entonces se escriben sus nombres separados por comas.

NOTA: También se puede acceder a una tabla en forma exclusiva si se abre la transacción como SNAPSHOT TABLE STABILITY pero esa sí que sería una pésima idea porque en ese caso se estaría bloqueando las tablas cabecera de ventas y detalles de ventas a todos los usuarios. Nadie podría no solamente insertar sino tampoco actualizar o borrar.

Conclusión:

En Firebird no se recomienda tener acceso exclusivo a una tabla. Si normalmente requieres tener acceso exclusivo entonces lo estás haciendo mal y no entiendes como funcionan las transacciones en Firebird. Sin embargo, hay algunos poquísimos casos en que sí es necesario el acceso exclusivo y es por ese motivo que tenemos la posibilidad de hacerlo. En este artículo hemos visto un ejemplo de uno de esos poquísimos casos.

NOTA: Aunque en este artículo no escribí sobre eso, quizás te hayas dado cuenta por tí mismo de la gran importancia que tiene que las transacciones sean cortas. O sea: que terminen lo antes posible. Y es que cuanto más demore una transacción en finalizar mayor es la probabilidad de que ocurran conflictos con otras transacciones. Una transacción que se demora 1 milisegundo muy raramente tendrá conflictos, pero una que se demora media hora …

Artículos relacionados:

Entendiendo a las transacciones

El índice del blog Firebird21