Mostrando los resultados ordenados por cualquier criterio

8 comentarios

Cuando en un SELECT usamos la cláusula ORDER BY le estamos indicando al Firebird en cual orden queremos que las filas sean mostradas. En general ese orden es fijo y ya lo conocemos antes de escribir el SELECT. Sin embargo, en ocasiones no ocurre así sino que el orden de las filas depende de alguna condición.

Ejemplo:

Tenemos una tabla llamada PRODUCTOS la cual tiene las siguientes filas:

ORDEN1

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

Y queremos mostrar a esas filas ordenadas según un criterio muy particular, y no tenemos un índice creado que podamos usar. En este caso, lo que queremos es mostrar primero a todas las filas en cuyo nombre esté “350”, luego las filas en cuyo nombre está la palabra “LITRO” y luego las filas en cuyo nombre está la palabra “LITROS”.

Resumiendo:

  • Primero las filas que tienen “350”
  • Después las filas que tienen “LITRO”
  • Después las filas que tienen “LITROS”

Con el siguiente SELECT … ORDER BY obtendemos lo que buscamos:

SELECT
   PRD_IDENTI,
   PRD_NOMBRE,
   CASE
      WHEN PRD_NOMBRE CONTAINING '350'    THEN 1
      WHEN PRD_NOMBRE CONTAINING 'LITROS' THEN 3
      WHEN PRD_NOMBRE CONTAINING 'LITRO'  THEN 2
   END
FROM
   PRODUCTOS
ORDER BY
   CASE
      WHEN PRD_NOMBRE CONTAINING '350'    THEN 1
      WHEN PRD_NOMBRE CONTAINING 'LITROS' THEN 3
      WHEN PRD_NOMBRE CONTAINING 'LITRO'  THEN 2
   END

El primer CASE … END es opcional, no necesitamos escribirlo pero si lo escribimos nos ayuda a entender lo que sucede. El resultado será el siguiente:

ORDEN2

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

Entonces ¿por qué funciona lo que escribimos en la cláusula ORDER BY? porque hemos creado una columna virtual, y las filas se muestran ordenadas según esa columna virtual. O sea, primero todas las que tiene el valor 1, luego las que tienen el valor 2 y finalmente las que tienen el valor 3.

Desde luego que podríamos tener más valores si los necesitamos: 4, 5, 6, 7, …., etc.

¿Y por qué los WHEN que escribimos no están ordenados de menor a mayor?

Porque los caracteres “LITRO” está incluidos dentro de los caracteres “LITROS”. Si hubiéramos escrito primero el WHEN que tiene “LITRO” y luego el WHEN que tiene “LITROS” entonces no habríamos obtenido el resultado deseado. ¿Por qué? porque en ese caso “LITROS” habría tenido el valor 2 y no el valor 3, que es el que necesitamos.

Conclusión:

Es importante recordar que podemos mostrar a las filas por cualquier orden que se nos ocurra, y que no es necesario tener un índice para ello, y que la condición puede ser cualquiera. La técnica es crear una columna virtual (la cual por supuesto no es necesario mostrársela a los usuarios) y así las filas serán mostradas según el orden en que las hayamos colocado en esa columna virtual.

En este ejemplo se mostró la columna virtual, pero eso es para que se entienda la técnica, a los usuarios no necesitamos mostrársela.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

Un error de concepto en la cláusula WHERE

4 comentarios

A veces, escribimos un SELECT, nos parece que está todo bien, pero sin embargo no es así. Lo miramos de arriba a abajo, de abajo a arriba y no encontramos el problema pero sin embargo … el SELECT no funciona.

Lo más probable es que ya te haya ocurrido algo así, y si ese no es el caso, no te apures … ya te ocurrirá.

Un caso de esos ví hace unos días y me pareció instructivo para mostrarlo aquí en el blog. Veamos:

Consulta 1. Un SELECT que sí funciona

SELECT
   PER_NOMBRE,
   PER_APELLD,
   CASE
      WHEN PER_ESTCIV = 1 THEN 'Soltero'
      WHEN PER_ESTCIV = 2 THEN 'Casado'
      WHEN PER_ESTCIV = 3 THEN 'Separado'
      WHEN PER_ESTCIV = 4 THEN 'Divorciado'
      WHEN PER_ESTCIV = 5 THEN 'Viudo'
   END
FROM
   PERSONAS

Este SELECT está todo ok, funcionará perfectamente, nos mostrará el nombre de una persona, su apellido, y su estado civil. El estado civil está codificado con un número que va del 1 al 5 pero lo que veremos será una palabra y eso está ok.

Consulta 2. Un SELECT que no funciona

SELECT
   PER_NOMBRE,
   PER_APELLD
FROM
   PERSONAS
WHERE
   CASE
      WHEN PER_ESTCIV = 1 THEN 'Soltero'
      WHEN PER_ESTCIV = 2 THEN 'Casado'
      WHEN PER_ESTCIV = 3 THEN 'Separado'
      WHEN PER_ESTCIV = 4 THEN 'Divorciado'
      WHEN PER_ESTCIV = 5 THEN 'Viudo'
   END

Este SELECT es muy parecido al anterior, pero sin embargo no funcionará. Lo que se hizo fue pasar el CASE en el WHERE. Eso no es un problema en sí porque podemos tener un CASE en el WHERE, sin embargo este SELECT no funciona.

¿Por qué?

Ese SELECT fue visto por gente con bastante experiencia en Firebird pero sin embargo no detectaron el problema. O tardaron en detectarlo. El autor de este blog detectó el problema al instante pero le extrañó mucho que gente experimentada con Firebird tardara tanto tiempo en encontrar el error (el SELECT problemático en realidad era otro, con otras columnas, otra tabla, muchas más columnas, pero la idea es la misma, y aquí se la muestra simplificada).

¿Dónde está el problema? ¿ya lo encontraste?

Si no es así, vuelve a mirar el SELECT y trata de encontrarlo antes de mirar la solución, porque si miras la solución no estarás usando tu cerebro.

¿Y?

¿Por qué el SELECT mostrado en la Consulta 2. no funciona?

Bien, la respuesta correcta es “porque no está completo”. Le está faltando algo. ¿Qué le está faltando?

Para saber lo que le está faltando debemos pensar en el WHERE. ¿Qué escribimos en el WHERE? Una condición para filtrar las filas que serán mostradas. Ok, todo bien hasta ahí. ¿Y cuáles son los valores posibles de una condición? que se cumple o no se cumple. En SQL eso podríamos traducir como: debe devolver verdadero o falso. Y también NULL es posible.

¿Y entonces?

Entonces, como puedes ver, al SELECT de la Consulta 2. le está faltando una comparación. El valor devuelto por el CASE debe ser comparado con algo, no puede quedarse así como está, porque está en el aire y eso está mal. Sea lo que sea que escribamos en el WHERE nos debe devolver verdadero o falso o NULL. Si devuelve cualquier otra cosa (o no devuelve algo) entonces está mal, no funcionará.

Y ese es el error de concepto. Se escribió un WHERE que no devuelve verdadero ni falso ni NULL.

Por lo tanto, un SELECT corregido y sí funcionando sería similar al siguiente:

SELECT
   PER_NOMBRE,
   PER_APELLD
FROM
   PERSONAS
WHERE
   CASE
      WHEN PER_ESTCIV = 1 THEN 'Soltero'
      WHEN PER_ESTCIV = 2 THEN 'Casado'
      WHEN PER_ESTCIV = 3 THEN 'Separado'
      WHEN PER_ESTCIV = 4 THEN 'Divorciado'
      WHEN PER_ESTCIV = 5 THEN 'Viudo'
   END
   = 'Casado'

Que nos devolverá los nombres y apellidos de las personas casadas. Ahora sí el SELECT está completo porque comparamos el valor devuelto por el CASE con algo, entonces ya está todo ok, y funcionará a la perfección.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

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

Capturando excepciones

6 comentarios

Las excepciones son una importante herramienta que el Firebird pone a nuestra disposición para que las utilicemos cuando ocurre un error dentro de un stored procedure o de un trigger.

¿Qué significa “capturar una excepción”?

Que podemos decidir nosotros lo que se hará cuando ocurre un error, en lugar de dejar que el Firebird haga sus acciones predeterminadas.

Por lo tanto:

    • Si no capturamos la excepción, el Firebird decide que acción tomar
    • Si capturamos la excepción, nosotros decidimos que acción tomar

¿Dónde se pueden capturar las excepciones?

En los stored procedures y en los triggers

¿Cómo se llama el bloque de comandos que nosotros escribimos para decidir qué acción tomar cuándo ocurre un error?

Manejador de la excepción

¿Por qué necesitamos escribir manejadores de excepción?

Porque así podemos responder al error y quizás corregirlo

¿Cómo se captura una excepción?

Usando la palabra clave WHEN y un bloque BEGIN … END

¿Cómo se le dice al Firebird cuáles errores se desea controlar?

Con una lista de esos errores, que puede ser:

  1. Código de error SQL de una excepción del sistema
  2. Nombre de una excepción del sistema
  3. Nombre de una excepción del usuario
  4. Escribiendo “ANY” que significa que todos los errores serán manejados

¿Cuáles son las acciones que toma el Firebird cuándo ocurre un error?

  • Busca un manejador para la excepción en el stored procedure o trigger actual
  • Deshace todas las acciones que había realizado hasta ese momento en el stored procedure o trigger actual
  • Regresa al nivel superior y busca un manejador de la excepción allí. Continúa subiendo niveles hasta que encuentra un manejador para la excepción. Si ningún manejador de excepción es encontrado entonces devuelve el control al programa llamador, informándole del error ocurrido
  • Si encontró un manejador para la excepción entonces ejecuta todas las instrucciones que hay en ese manejador de excepción y luego continúa con la línea siguiente a la que invocó al stored procedure o trigger que tenía el error. Si ese stored procedure o trigger estaba en el nivel más alto, entonces el control regresa a la aplicación

Si un error es capturado en un manejador de excepción, entonces no se informa de ese error a la aplicación.

¿Qué significa “relanzar la excepción”?

Que después de capturar la excepción y de manejar el error ocurrido podemos pedirle al Firebird que continúe con sus acciones por defecto, o sea las que hubiera hecho de no haber sido capturada la excepción.

¿Cómo se relanza una excepción?

Escribiendo la palabra:

EXCEPTION;

Sin nada más a su derecha que el punto y coma

¿Qué podemos hacer cuándo ocurre un error?

  1. Nada. En este caso el Firebird se encargará de su manejo
  2. Capturarlo. En este caso nosotros nos encargaremos de su manejo
  3. Capturarlo y relanzarlo. Que es una mezcla de los dos anteriores. Primero, nosotros nos encargamos de su manejo y luego le pedimos al Firebird que se encargue él

Ejemplo 1:

CREATE PROCEDURE CAPTURAR_EXCEPCION_1
AS
   DECLARE VARIABLE lnMiNumero INTEGER;
   DECLARE VARIABLE lnResultado INTEGER;
BEGIN

   lnMiNumero = 21;

   lnResultado = lnMiNumero / 0;

END

Como puedes ver, aquí hay una división por cero, la cual como seguramente sabes no está permitida en Matemática. Entonces, si ejecutamos ese stored procedure el Firebird nos mostrará el mensaje:

“Arithmetic exception, numeric overflow, or string truncation.

Integer divide by zero. The code attempted to divide an integer value by an integer divisor of zero.
At procedure ‘CAPTURAR_EXCEPCION_1’ line: 9, col: 4.

SQL Code: -802
IB Error Number: 335544321″

¿Qué podríamos escribir para capturar ese error?

Ejemplo 2:

CREATE PROCEDURE CAPTURAR_EXCEPCION_2
AS
   DECLARE VARIABLE lnMiNumero INTEGER;
   DECLARE VARIABLE lnResultado INTEGER;
BEGIN

   lnMiNumero = 21;

   lnResultado = lnMiNumero / 0;

   WHEN GDSCODE ARITH_EXCEPT DO BEGIN -- Ocurrió una excepción aritmética

   END

END

Aquí le estamos diciendo al Firebird que si ocurre un error aritmético, cualquiera sea ese error, que lo ignore. Como dentro del manejador de la excepción (el bloque WHEN … END es el manejador de la excepción) no hemos escrito algún comando, entonces nada se hará, el efecto será ignorarlo al error ocurrido. Por supuesto que podríamos escribir algo, como vemos a continuación:

Ejemplo 3:

CREATE PROCEDURE CAPTURAR_EXCEPCION_3
AS
   DECLARE VARIABLE lnMiNumero INTEGER;
   DECLARE VARIABLE lnResultado INTEGER;
BEGIN

   lnMiNumero = 21;

   lnResultado = lnMiNumero / 0;

   WHEN GDSCODE ARITH_EXCEPT DO BEGIN -- Ocurrió una excepción aritmética
      lnResultado = -999;
   END

END

Ahora le estamos diciendo que si ocurre un error de división por cero le asigne a la variable lnResultado el valor -999

Ejemplo 4:

CREATE PROCEDURE CAPTURAR_EXCEPCION_4
AS
   DECLARE VARIABLE lnMiNumero INTEGER;
   DECLARE VARIABLE lnResultado INTEGER;
BEGIN

   lnMiNumero = 21;

   lnResultado = lnMiNumero / 0;

   WHEN GDSCODE ARITH_EXCEPT DO BEGIN -- Ocurrió una excepción aritmética
      lnResultado = -999;
      EXCEPTION;
   END

END

Aquí estamos haciendo dos cosas cuando ocurre una división por cero. Primero, le asignamos a la variable lnResultado el valor -999 y segundo, le pedimos al Firebird que haga lo que normalmente hace cuando encuentra una división por cero.

Conclusión:

Si capturamos los errores entonces tenemos muchísimo más control sobre lo que ocurre dentro de los stored procedures y de los triggers. Hemos visto varias formas de manejar las excepciones (o sea, los errores) y en siguientes artículos veremos aún más.

Artículos relacionados:

Entendiendo las excepciones

El índice del blog Firebird21

El foro del blog Firebird21