Hallando la última palabra

2 comentarios

Ya vimos como hallar la primera palabra de un texto, ¿pero y si necesitamos hallar la última palabra, cómo lo conseguimos?

LEFT1

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

Listado 1.

SELECT
   RIGHT(ALU_NOMBRE, POSITION(' ' IN REVERSE (ALU_NOMBRE) || ' '))
FROM
   ALUMNOS

RIGHT1

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

Explicación:

La función POSITION() devuelve la posición en la cual se encuentra un string dentro de otro string. La función REVERSE() lo muestra de atrás para adelante. Si hay al menos un espacio en blanco entonces la función RIGHT() en el Listado 1. nos devolverá la última palabra. ¿Pero y si no hay ese espacio en blanco? Entonces devolverá un string vacío. Para evitarlo, se concatena un espacio en blanco y así se soluciona.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

Poniendo los JOIN en el orden correcto

1 comentario

Muchas veces cuando escribimos un SELECT que no es muy sencillo necesitamos relacionar a una tabla con otra u otras tablas. Eso lo hacemos con las cláusulas JOIN.

Como sabemos, hay dos clases de JOIN:

  • INNER JOIN
  • OUTER JOIN

La palabra INNER puede omitirse si se desea, ya que es la que por defecto asumirá el Firebird si no se escribe algo antes de la palabra JOIN. Los OUTER a su vez pueden ser: LEFT, RIGHT, FULL

Entonces, la pregunta es: ¿importa si se escribe primero un INNER o un OUTER? ¿O es lo mismo?

Consulta 1. Poniendo primero el INNER JOIN

SELECT
   *
FROM
   MiTabla1
JOIN
   MiTabla2
      ON MiCondición1
LEFT JOIN
   MiTabla3
      ON MiCondición2

Aquí se escribió primero el INNER JOIN (la palabra INNER no es obligatorio escribir) y luego el OUTER JOIN (la palabra OUTER tampoco es obligatorio escribir, al escribir LEFT ya el Firebird sabe que se trata de un OUTER)

Consulta 2. Poniendo primero el OUTER JOIN

SELECT
   *
FROM
   MiTabla1
LEFT JOIN
   MiTabla3
      ON MiCondición2
JOIN
   MiTabla2
      ON MiCondición1

Aquí se escribió primero el OUTER JOIN (en este caso, fue un LEFT) y luego el INNER JOIN (la palabra INNER es opcional, no se requiere escribirla)

¿Cuál consulta es mejor?

¿La Consulta 1 es más eficiente que la Consulta 2? ¿La Consulta 2 es más eficiente que la Consulta 1? ¿O son igual de eficientes?

El optimizador del Firebird solamente reordena las tablas en el PLAN hasta que encuentra el primer OUTER JOIN (sea LEFT, RIGHT, o FULL).

Por lo tanto, la correcta es la Consulta 1.

Conclusión:

El orden de los JOIN sí importa en Firebird porque el optimizador reordena las tablas para obtener un mejor PLAN solamente hasta que encuentra el primer OUTER JOIN.

¿Consejo?

Revisa todos tus SELECTs y todas tus vistas para asegurarte de que siempre los INNER JOIN se encuentren antes de los OUTER JOIN, conseguirás consultas más eficientes de esa manera.

Artículos relacionados:

INNER JOIN y OUTER JOIN

Ejemplo Nº 001 – Usando INNER JOIN

Ejemplo Nº 002 – Usando INNER JOIN eficientemente

Ejemplo Nº 003 – Escribiendo varios INNER JOIN

Ejemplo Nº 004 – Usando un INNER JOIN para autoreferenciar una tabla

Ejemplo Nº 006 – Usando LEFT JOIN e INNER JOIN

JOIN implícito y JOIN explícito

NATURAL JOIN y CROSS JOIN

Relacionando dos tablas: la forma vieja y la forma nueva

Teoría de Conjuntos: Unión, Intersección, Diferencia

Entendiendo las tablas autoreferenciadas

Autoreferenciar una tabla. Algunos ejemplos

El índice del blog Firebird21

El foro del blog Firebird21

Arithmetic overflow or division by zero has occurred

Deja un comentario

Si ves el mensaje: “Arithmetic overflow or division by zero has occurred. Arithmetic exception, numeric overflow, or string truncation. String right truncation”

¿Qué significa?

Que el Firebird encontró un error grave y por eso detuvo el procesamiento. Ese error grave pudo ser debido a un error matemático (por ejemplo, división por cero), a un sobreflujo (se quiso guardar en una columna numérica un número mayor al máximo permitido), o un error de cadena (se quiso guardar en una columna una cadena de mayor longitud que la definida).

La última frase: “String right truncation” nos da la pista de cual de esos errores fue detectado. En este caso, se quiso guardar una cadena de mayor longitud que la definida.

Esto puede ocurrir en dos ocasiones típicas:

  1. Al querer hacer un INSERT o un UPDATE a una columna
  2. Al querer hacer un SELECT a una vista cuya tabla ha cambiado su estructura

Caso 1. Al querer hacer un INSERT o un UPDATE a una columna

La columna por ejemplo está definida como VARCHAR(30) y queremos guardar en ella más de 30 caracteres

¿Solución? Aumentar el ancho que la columna tiene en la tabla o disminuir la cantidad de caracteres a guardar en la columna

Caso 2. Al querer hacer un SELECT a una vista cuya tabla ha cambiado su estructura

La columna por ejemplo está definida como VARCHAR(30), creamos una vista que usa esa columna, luego modificamos la columna a VARCHAR(40), al hacer SELECT de la vista obtenemos el error. ¿Por qué? porque la vista es un SELECT compilado y se compiló cuando la columna estaba definida como VARCHAR(30), eso es lo que conoce la Base de Datos. Si más tarde cambiamos la longitud de la columna a 40 la vista no está enterada de ese cambio, detecta una inconsistencia y muestra el error.

¿Solución?

Volver a compilar la vista. Al recompilar la vista, ésta ya usará la nueva longitud.

Para que el cambio tenga efecto deberás desconectarte de la Base de Datos y volver a conectarte.

Artículo relacionado:

El índice del blog Firebird21

 

Hallar los movimientos ocurridos entre dos fechas (otro método)

5 comentarios

En este artículo ya habíamos visto un método para hallar todos los movimientos (compras, ventas, cobranzas, pagos, etc.) que ocurrieron entre dos fechas dadas, y si en una fecha no hubo movimientos entonces mostrarla igual pero con un total de cero.

https://firebird21.wordpress.com/2014/04/29/hallando-todas-las-ventas-entre-dos-fechas-dadas/

Ahora, veremos otro método con el cual podremos obtener los mismos resultados, gracias a la colaboración de Claudio Martín.

Aquí, lo que haremos será crear un stored procedure seleccionable que nos devolverá todas las fechas de un rango dado, y luego cuando las necesitemos mediante un LEFT JOIN o un RIGHT JOIN las obtendremos.

Este método tiene la ventaja de que el stored procedure seleccionable que escribimos es uno solo y lo podemos utilizar en multitud de ocasiones diferentes, todas las veces que necesitemos un rango de fechas.


CREATE PROCEDURE RANGO_FECHAS(
      tdFecIni DATE,
      tdFecFin DATE)
   RETURNS(
      ftdFecha DATE)
AS
   DECLARE VARIABLE ldFecha DATE;
BEGIN

   ldFecha = tdFecIni;

   WHILE (ldFecha <= tdFecFin) DO BEGIN
      ftdFecha = ldFecha;
      SUSPEND;
      ldFecha = ldFecha + 1;
   END

END;

Entonces, usando la misma tabla que en el artículo anterior, escribiríamos:

SELECT
   R.ftdFecha,
   SUM(COALESCE(M.MVC_TOTALX, 0)) AS TOTAL_VENTAS_DIA
FROM
   RANGO_FECHAS('01/01/2014', '01/07/2014') R
LEFT JOIN
   MOVIMCAB M
      ON R.ftdFecha = M.MVC_FECHAX
GROUP BY
   R.ftdFecha

Y obtendríamos este resultado:

VENTAS1

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

El cual, como puedes verificar, es exactamente igual al obtenido con el otro método. Pero la ventaja de este nuevo método es que el stored procedure seleccionable podemos usarlo siempre que necesitemos obtener todas las fechas de un rango dado.

 Conclusión:

Una de las muchas cosas buenas que tiene el Firebird es que nos permite obtener los mismos resultados usando métodos diferentes, queda a nuestro criterio elegir el que nos parece más conveniente para cada situación particular.

En este caso, podemos tener un stored procedure que cuando las necesitemos nos devolverá todas las fechas de un rango, para ello simplemente lo juntamos mediante LEFT JOIN o RIGHT JOIN a la otra tabla y listo, ya está.

Artículos relacionados:

Hallando todas las ventas entre dos fechas dadas

Entendiendo a los Stored Procedures

El índice del blog Firebird21

 

Eliminando códigos de control en las cadenas alfanuméricas

4 comentarios

Como seguramente sabes, todos los caracteres que usas para escribir textos con la computadora tienen un código ASCII. Así por ejemplo el código ASCII de la letra A mayúscula es 65, el código ASCII del dígito 0 es 48, etc.

Los códigos ASCII cuyos valores van desde al 0 al 31 en su mayoría no son imprimibles y se llaman “códigos de control”.

Si una columna CHAR o VARCHAR tiene códigos de control entonces el resultado que obtendrás al ejecutar un SELECT o al utilizar funciones alfanuméricas tales como LEFT(), TRIM(), etc. podría no ser el esperado.

Entenderemos mejor el problema con el siguiente ejemplo.

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

ASCII1

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

Ahora, reemplazamos todos los espacios en blanco por el código de control 0 (cero):

UPDATE
   PRODUCTOS
SET
   PRD_NOMBRE = REPLACE(PRD_NOMBRE, ' ', ASCII_CHAR(0))

Volvemos a consultar el contenido de la tabla PRODUCTOS y ahora esto es lo que obtenemos:

ASCII2

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

¿Qué pasó aquí? ¡¡¡Desaparecieron todos los caracteres que se encontraban después del primer espacio en blanco!!!

Pues no, no te asustes, no han desaparecido sino que no son visibles. Cuando el Firebird encuentra un caracter cuyo código ASCII es cero ya no muestra los siguientes caracteres, llega solamente hasta el que tiene código ASCII cero.

Entonces, si ahora actualizamos nuestra tabla PRODUCTOS de forma inversa (o sea, reemplazando los códigos ASCII cero con espacios en blanco):

UPDATE
   PRODUCTOS
SET
   PRD_NOMBRE = REPLACE(PRD_NOMBRE, ASCII_CHAR(0), ' ')

esto será lo que obtendremos:

ASCII1

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

¡¡¡Voilá!!! ¡¡¡Reaparecieron todos los caracteres faltantes!!!

Conclusión:

Si al consultar el contenido de una columna alfanumérica notas que faltan caracteres o que el comportamiento de las funciones alfanuméricas LEFT(), RIGHT(), TRIM(), etc., es extraño entonces es posible que la causa sea que hay caracteres de control en esa columna. Puedes utilizar la función REPLACE() para reemplazar a esos caracteres de control por otros caracteres, típicamente por espacios en blanco, como has visto en los ejemplos anteriores y de esa manera solucionarás el problema.

Artículo relacionado:

El índice del blog Firebird21

INNER JOIN y OUTER JOIN

20 comentarios

En la literatura SQL frecuentemente encontrarás estos términos: INNER JOIN y OUTER JOIN.

¿Qué significan y cuál es la diferencia entre ellos?

Un JOIN (juntar) es una relación entre dos tablas. Esas tablas pueden ser de cualquier tipo: tablas reales, vistas, tablas CTE, tablas GTT, etc. Si quieres (y puedes) relacionarlas entonces es posible usar un JOIN.

Un INNER JOIN (juntar internamente) requiere que para cada fila de la tabla A exista una fila en la tabla B. Por eso se dice que es “interno” porque es un requisito que todas las filas puedan emparejarse.

Un OUTER JOIN (juntar externamente) no requiere que para cada fila de la tabla A exista una fila en la tabla B. Se dice que es “externo” porque en el resultado podrían mostrarse filas que no fueron emparejadas. Cuando dos filas no pueden emparejarse se coloca un NULL en las columnas donde no existen datos.

Recuerda que en SQL la palabra NULL significa “desconocido”.

¿Por qué usar un OUTER JOIN, cuál sería el beneficio?

Cuando usamos un INNER JOIN mostramos datos que actualmente existen en ambas tablas. En cambio cuando usamos un OUTER JOIN podemos mostrar datos que no existen en una de las tablas.

Por ejemplo, si relacionamos “internamente” la tabla ALUMNOS con la tabla EXÁMENES podemos mostrar los datos de todos los alumnos que fueron examinados, pero solamente de ellos, no podríamos saber cuales fueron los alumnos que por algún motivo no fueron examinados. En cambio si las relacionamos “externamente” podemos mostrar los datos de todos los alumnos, hayan sido examinados o no.

Si relacionamos “internamente” las tablas PRODUCTOS y VENTAS podemos mostrar los datos de todos los productos vendidos. Pero no podríamos saber cuales fueron los productos que no se vendieron. En cambio si relacionamos esas tablas “externamente” sí podríamos saber cuales productos no se vendieron.

Tipos de OUTER JOIN

Hay tres tipos de OUTER JOIN, dependiendo si los datos que sí o sí serán mostrados se encuentran en la primera tabla, en la segunda tabla, o en ambas tablas.

LEFT OUTER JOIN: Los datos de la tabla de la izquierda (LEFT) se muestran sí o sí. Los de la tabla de la derecha se muestran solamente si se los pudo emparejar, en caso contrario se muestra NULL.

RIGHT OUTER JOIN: Los datos de la tabla de la derecha (RIGHT) se muestran sí o sí. Los de la tabla de la izquierda se muestran solamente si se los pudo emparejar, en caso contrario se muestra NULL.

FULL OUTER JOIN: Se muestran todas las filas de cada tabla, poniendo NULL cuando no se puede emparejar.

Equivalencia entre INNER JOIN y OUTER JOIN

Hay casos en que se obtiene exactamente el mismo resultado si usamos un INNER JOIN o si usamos un OUTER JOIN. Eso se da cuando entre las dos tablas existe una restricción Foreign Key y nuestro OUTER JOIN es:

  • TablaPadre LEFT OUTER JOIN TablaHija
  • TablaHija RIGHT OUTER JOIN TablaPadre

O sea que en los tres casos siguientes obtendremos exactamente el mismo resultado:

La tabla VENTAS es la tabla hija, y la tabla PRODUCTOS es la tabla padre. Eso significa que todos los productos vendidos deben existir.

SELECT
   Columnas
FROM
   VENTAS V
INNER JOIN
   PRODUCTOS P
      ON MiCondición
SELECT
   Columnas
FROM
   PRODUCTOS P
LEFT OUTER JOIN
   VENTAS V
      ON MiCondición
WHERE
   V.Identificador IS NOT NULL
SELECT
   Columnas
FROM
   VENTAS V
RIGHT OUTER JOIN
   PRODUCTOS P
      ON MiCondición
WHERE
   V.Identificador IS NOT NULL

Al autor de este blog le parece más sencilla y más fácil de entender la primera de las tres alternativas, pero sobre gustos …

Precaución:

Algo muy IMPORTANTE a recordar es que los JOIN puedes usar para relacionar dos tablas cualesquiera aunque no exista una restricción Foreign Key entre ellas, pero tal cosa puede ser muy peligrosa porque en la mayoría de los casos obtendrías datos que no tienen sentido … aunque parecerán tenerlos. Esto no significa que no se puedan relacionar tablas que no tienen una restricción Foreign Key, claro que sí pueden relacionarse pero con mucho cuidado porque de lo contrario se mostrarán resultados inconsistentes.

En otras palabras, lo seguro es hacer el JOIN entre dos tablas que tienen una restricción Foreign Key entre ellas, si no tienen esa restricción, hmmmmmmmm.

Artículo relacionado:

El índice del blog Firebird21

Introducción a los SELECTs para intermedios

1 comentario

Los SELECTs que encontrarás en esta categoría son más complicados que los SELECTs para principiantes que tienes en:

https://firebird21.wordpress.com/category/principiantes/ejemplos-de-selects-para-principiantes/

Los SELECTs para intermedios usan más de una tabla porque es raro que en la vida real todos los datos que necesites estén en una sola tabla, lo normal es que estén en dos o más tablas.

En SQL para relacionar a dos tablas entre sí se utiliza la cláusula JOIN. Al resultado obtenido a su vez le puedes hacer un JOIN con otra tabla. Y a ese nuevo resultado le puedes hacer otro JOIN. Y así sucesivamente. Por lo tanto una sola consulta (un solo SELECT) puede tener muchos JOIN.

Para que el JOIN pueda efectuarse debe existir una columna (o más de una columna) igual o similar entre ambas tablas. Si tus tablas están bien diseñadas (o sea, si están normalizadas) entonces esas columnas seguro que existen.

También es posible relacionar una tabla con sí misma. Para ello necesitarás que dos (o más) columnas tengan alguna relación entre ellas.

La cláusula JOIN puede tener un modificador, los modificadores posibles son:

  • INNER o dejar en blanco porque es el valor por defecto
  • LEFT o LEFT OUTER (son sinónimos)
  • RIGHT o RIGHT OUTER (son sinónimos)
  • FULL o FULL OUTER (son sinónimos)

El modificador INNER devuelve las filas cuya columna/s de relación se encuentran en ambas tablas. Es lo mismo que hace el comando SET RELATION en el lenguaje Visual FoxPro. Este es el modificador por defecto o sea el modificador que se usará en el JOIN si ninguno es especificado.

El modificador LEFT devuelve todas las filas que se encuentran en la tabla de la izquierda (o sea, la tabla escrita a continuación de la cláusula FROM) y solamente las filas de la tabla de la derecha que cumplen la relación.

El modificador RIGHT devuelve todas las filas que se encuentran en la tabla de la derecha (o sea, la tabla escrita a continuación de la cláusula JOIN) y solamente las filas de la tabla de la izquierda que cumplen la relación.

El modificador FULL devuelve todas las filas de la tabla de la izquierda (o sea, la tabla escrita a continuación de la cláusula FROM) y todas las filas de la tabla de la derecha (o sea, la tabla escrita a continuación de la clásula JOIN).

El siguiente gráfico te ayudará a entender mejor a esos modificadores:

Visual_SQL_JOINS

(haciendo click en el gráfico lo verás más grande)

Siempre que tengas alguna duda sobre como funcionan los modificadores del JOIN (INNER, LEFT, RIGHT, FULL) revisa este gráfico que te ayudará a entender.

Recuerda que si no escribes un modificador se usará INNER.

En los siguientes artículos de esta categoría veremos muchos ejemplos del uso de JOIN.