Error: Foreign key references are present for the record

Deja un comentario

Este error ocurre cuando una tabla tiene una restricción Foreign Key y quieres borrar una fila de la tabla padre que está siendo referenciada en la tabla hija y la Foreign Key no es “on cascade”.

Artículo relacionado:

El índice del blog Firebird21

¿CHAR o VARCHAR? ¿cuál es mejor?

4 comentarios

Cuando declaras una columna alfanumérica puedes elegir entre CHAR y VARCHAR. ¿Cuál de esos tipos de datos es mejor?

Guardando columnas CHAR en .DBF y en Paradox

En las tablas .DBF y en las tablas de Paradox, si declaras una columna como CHAR y guardas en esa columna menos caracteres que los declarados se la completa con espacios en blanco.

Ejemplo:

Si una columna de una tabla .DBF está declarada como CHAR(20) y guardas en esa columna:

‘HOLA’, se le agregan 16 espacios en blanco, para completar los 20 declarados

‘ASUNCIÓN’, se le agregan 12 espacios en blanco para guardar los 20 declarados

‘AMERICA’ se le agregan 13 espacios en blanco para completar los 20 declarados

Guardando columnas CHAR en Firebird

Firebird no agrega espacios en blanco al final porque cuando se guarda una fila, la fila completa (con todas sus columnas CHAR, VARCHAR, INTEGER, DATE, TIME, etc.) es comprimida usando el algoritmo RLE.

Se hace así para ahorrar espacio en el disco duro porque la fila comprimida ocupa menos espacio que la fila sin comprimir.

Ejemplo:

Una columna de una tabla Firebird está declarada como CHAR(20) y guardas en esa columna:

‘HOLA’, ocupa 4 caracteres

‘ASUNCIÓN’, ocupa 8 caracteres

‘AMÉRICA’, ocupa 7 caracteres

Como puedes ver Firebird solamente guarda los datos significativos, no agrega espacios en blanco al final.

Guardando columnas VARCHAR en Firebird

Las columnas de tipo VARCHAR ocupan 2 bytes más que los declarados porque se usan 2 bytes para conocer la longitud de la cadena guardada.

Ejemplo:

Una columna de una tabla Firebird está declarada como VARCHAR(20) y guardas en esa columna:

‘HOLA’, ocupa 6 caracteres (2 que indican la longitud más 4 de la palabra ‘HOLA’)

‘ASUNCIÓN’, ocupa 10 caracteres (2 que indican la longitud más 8 de la palabra ‘ASUNCIÓN’)

‘AMÉRICA’, ocupa 9 caracteres (2 que indican la longitud más 7 de la palabra ‘AMÉRICA’)

Ahorrando espacio en el disco duro

Si lo que necesitas es ahorrar espacio en el disco duro entonces CHAR es más eficiente que VARCHAR porque como has visto cada columna CHAR ocupa 2 bytes menos que su correspondiente columna VARCHAR.

Espacio ocupado en la memoria RAM

En ambos casos la columna ocupa el espacio declarado.

Ejemplo:

Una columna CHAR(20) ocupa 20 bytes en la memoria RAM

Una columna VARCHAR(20) ocupa 20 bytes en la memoria RAM

Consultando columnas CHAR y VARCHAR

Cuando ejecutas un comando SELECT sobre una columna CHAR Firebird le agrega los espacios en blanco necesarios para completar la longitud declarada.

Cuando ejecutas un comando SELECT sobre una columna VARCHAR Firebird ignora a los dos primeros caracteres (los que indican la longitud) y devuelve el resto de la cadena.

Por lo tanto, al usar CHAR se ahorran 2 bytes pero al hacer el SELECT de esa columna se pierde tiempo en rellenarla con los espacios en blanco faltantes.

Decidiendo entre CHAR y VARCHAR

Como hemos visto, al declarar una columna como CHAR se ahorra espacio en el disco duro (2 bytes menos que si la declaramos como VARCHAR) pero las consultas que involucran a columnas CHAR son más lentas que las que involucran a columnas VARCHAR porque las columnas CHAR deben ser rellenadas con espacios en blanco antes de ser mostradas.

Esos 2 bytes que se ahorran al declarar a la columna como CHAR, en esta época y con los discos duros gigantescos disponibles, pueden ser irrelevantes y no degradarán el rendimiento de la Base de Datos. La excepción es cuando la cantidad de caracteres a almacenar es pequeña, por ejemplo declarar una columna como VARCHAR(3) es un error porque en realidad se guardan en el disco duro 5 bytes (2 de la longitud más 3 declarados)  cuando usando CHAR(3) solamente necesitaríamos 3 bytes y además al ser la longitud tan pequeña (sólo 3 bytes) VARCHAR no sería más rápido que CHAR.

Por lo tanto:

  • Si la cantidad de caracteres a guardar en una columna es fija y menor que 80, usar CHAR
  • Si la cantidad de caracteres a guardar en una columna es fija o es variable y esa cantidad es menor o igual que 10, usar CHAR
  • Si la cantidad de caracteres a guardar en una columna es variable y es mayor que 10, usar VARCHAR
  • Si la cantidad de caracteres a guardar en una columna es variable y mayor que 10.000 suele ser preferible usar un BLOB de texto

Ejemplos donde se debería usar CHAR

  • Guardar el código del Estado (‘AK’, ‘AZ’, ‘NY’, ‘SC’, ‘TX’). Todos los códigos tienen 2 caracteres
  • Guardar el sexo (‘F’, ‘M’). Todos tienen 1 caracter
  • Guardar el prefijo telefónico (‘021’, ‘022’, ‘028’, ‘0293’). Todos los prefijos tienen 3 caracteres ó 4 caracteres

Ejemplos donde se debería usar VARCHAR

  • Guardar el nombre de la persona (‘ANA PAULA’, ‘CYNTHIA ELIZABETH’, ‘PATRICIA ADRIANA’). La cantidad de caracteres es variable
  • Guardar la dirección del proveedor (‘COLÓN 1234’, ‘HERNANDARIAS 3455’, ‘ESTADOS UNIDOS 56789’). La cantidad de caracteres es variable

Artículo relacionado:

El índice del blog Firebird21

Consultando datos que NO EXISTEN en una tabla

1 comentario

En las consultas lo normal es responder preguntas positivas, por ejemplo: ¿qué productos se vendieron hoy? ¿a cuáles clientes se les cobró hoy? ¿cuáles vendedores hicieron ventas hoy?

Todas esas preguntas son positivas porque los datos están guardados en las tablas y para responderlas lo que hacemos es consultar datos que se guardaron en las tablas.

Pero a veces necesitamos responder preguntas negativas, por ejemplo: ¿cuáles productos NO se vendieron hoy? ¿a cuáles clientes NO se les cobró hoy? ¿cuáles vendedores NO hicieron ventas hoy?

En Matemática, en la teoría de conjuntos, a esto se le llama “diferencia de conjuntos” y en Firebird tenemos dos formas de conseguirlo:

  1. Negamos la función EXISTS() para hallar la diferencia entre dos conjuntos
  2. Escribimos un LEFT JOIN y un NULL en la cláusula WHERE

Ejemplo:

Tenemos una tabla llamada PRODUCTOS que tiene estas filas:

DIFERENCIA1

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

y una tabla llamada MOVIMDET (detalles de los movimientos) que tiene estas filas:

DIFERENCIA2

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

donde en la columna MOV_IDEPRD se guarda el identificador del producto. Usamos esa columna para relacionar ambas tablas.

Método 1

Para saber cuales fueron los productos que NO SE VENDIERON escribimos esta consulta:

SELECT
   PRD_IDENTI,
   PRD_NOMBRE
FROM
   PRODUCTOS P
WHERE
   NOT EXISTS(SELECT
                 M.MOV_IDEPRD
              FROM
                 MOVIMDET M
              WHERE
                 M.MOV_IDEPRD = P.PRD_IDENTI)

y obtenemos este resultado:

DIFERENCIA3

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

donde como puedes ver se encuentran los productos que NO ESTÁN en la tabla MOVIMDET. En la tabla MOVIMDET los productos tienen los identificadores: 13, 14, 15, 16 y ningún producto con alguno de esos identificadores es mostrado en el resultado que obtuvimos.

Método 2

Podríamos obtener exactamente el mismo resultado escribiendo:

SELECT
   PRD_IDENTI,
   PRD_NOMBRE
FROM
   PRODUCTOS P
LEFT JOIN
   MOVIMDET M
      ON P.PRD_IDENTI = M.MOV_IDEPRD
WHERE
   M.MOV_IDEPRD IS NULL

Conclusión:

Con Firebird podemos responder preguntas positivas y preguntas negativas, hay que conocer las técnicas para responder las preguntas negativas porque pueden sernos útiles muchas veces.

Artículos relacionados:

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

El índice del blog Firebird21

Facturas que vencen hoy, en 7 días, en 15 días y en 30 días

Deja un comentario

Si necesitamos consultar cuales son las Facturas que debemos cobrar hoy, que debemos cobrar en 7 días, en 15 días, en 30 días (y por supuesto, en cualquier otra fecha) podemos hacerlo de varias maneras, aquí hay cuatro de ellas:

Ejemplo:

Tenemos una tabla llamada FACTURAS donde se encuentran las fechas de vencimiento de las Facturas de venta, la tabla tiene esta estructura:

FACTURAS1

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

y contiene estas filas:

FACTURAS2

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

Método 1. Usando UNION

Podemos usar el comando UNION para unir el resultado de una consulta con el resultado de otra consulta. En este caso, para diferenciar los resultados y saber a cual consulta pertenecen los resultados debemos agregarle una columna a cada consulta. En este ejemplo esa columna agregada se llama FECHA.

CREATE VIEW V_VENCIMIENTOS(
   FECHA,
   FAC_FECVEN,
   FAC_NUMERO,
   FAC_MONTOX)
AS

   SELECT
      'HOY' AS FECHA,
      FAC_FECVEN,
      FAC_NUMERO,
      FAC_MONTOX
   FROM
      FACTURAS
   WHERE
      FAC_FECVEN = CURRENT_DATE

   UNION

   SELECT
      'SEMANA' AS FECHA,
      FAC_FECVEN,
      FAC_NUMERO,
      FAC_MONTOX
   FROM
     FACTURAS
   WHERE
      FAC_FECVEN BETWEEN CURRENT_DATE + 1 AND CURRENT_DATE + 6

   UNION

   SELECT
      'QUINCENA' AS FECHA,
      FAC_FECVEN,
      FAC_NUMERO,
      FAC_MONTOX
   FROM
      FACTURAS
   WHERE
      FAC_FECVEN BETWEEN CURRENT_DATE + 7 AND CURRENT_DATE + 14

   UNION

   SELECT
      'MES' AS FECHA,
      FAC_FECVEN,
      FAC_NUMERO,
      FAC_MONTOX
   FROM
      FACTURAS
   WHERE
      FAC_FECVEN BETWEEN CURRENT_DATE + 15 AND CURRENT_DATE + 30;

Aquí, a las Facturas que vencen hoy les unimos las que vencen en la semana, les unimos las que vencen en la quincena y les unimos las que vencen en el mes. Se usa la cláusula BETWEEN para delimitar las fechas de las Facturas que serán mostradas y evitar así que una Factura se muestre más de una vez.

Escribimos esta consulta:

SELECT * FROM V_VENCIMIENTOS ORDER BY FAC_FECVEN

Y este es el resultado que obtenemos:

FACTURAS3

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

Donde cada Factura aparece en una fila y en la primera columna podemos ver si su vencimiento corresponde al día de hoy, a la semana, a la quincena o al mes.

Método 2. Usando un stored procedure

Aquí, en lugar de mostrar los vencimientos en filas los mostramos en columnas porque para algunas personas puede ser más descriptivo de esta manera:

CREATE PROCEDURE VENCIMIENTOS
   RETURNS(
      ftcNumero1 TYPE OF COLUMN FACTURAS.FAC_NUMERO,
      ftnMonto1  TYPE OF COLUMN FACTURAS.FAC_MONTOX,
      ftcNumero2 TYPE OF COLUMN FACTURAS.FAC_NUMERO,
      ftnMonto2  TYPE OF COLUMN FACTURAS.FAC_MONTOX,
      ftcNumero3 TYPE OF COLUMN FACTURAS.FAC_NUMERO,
      ftnMonto3  TYPE OF COLUMN FACTURAS.FAC_MONTOX,
      ftcNumero4 TYPE OF COLUMN FACTURAS.FAC_NUMERO,
      ftnMonto4  TYPE OF COLUMN FACTURAS.FAC_MONTOX)
AS
   DECLARE VARIABLE lcNumero TYPE OF COLUMN FACTURAS.FAC_NUMERO;
   DECLARE VARIABLE lnMontox TYPE OF COLUMN FACTURAS.FAC_MONTOX;
   DECLARE VARIABLE ldFecVen TYPE OF COLUMN FACTURAS.FAC_FECVEN;
BEGIN

   FOR SELECT
      FAC_NUMERO,
      FAC_MONTOX,
      FAC_FECVEN
   FROM
      FACTURAS
   WHERE
      FAC_FECVEN BETWEEN CURRENT_DATE AND CURRENT_DATE + 30
   ORDER BY
      FAC_FECVEN
   INTO
      :lcNumero,
      :lnMontox,
      :ldFecVen
   DO BEGIN
      ftcNumero1 = NULL;
      ftnMonto1  = NULL;
      ftcNumero2 = NULL;
      ftnMonto2  = NULL;
      ftcNumero3 = NULL;
      ftnMonto3  = NULL;
      ftcNumero4 = NULL;
      ftnMonto4  = NULL;
      IF (ldFecVen = CURRENT_DATE) THEN BEGIN
         ftcNumero1 = lcNumero;
         ftnMonto1  = lnMontox;
      END
      IF (ldFecVen BETWEEN CURRENT_DATE + 1 AND CURRENT_DATE + 6) THEN BEGIN
         ftcNumero2 = lcNumero;
         ftnMonto2  = lnMontox;
      END
      IF (ldFecVen BETWEEN CURRENT_DATE + 7 AND CURRENT_DATE + 14) THEN BEGIN
         ftcNumero3 = lcNumero;
         ftnMonto3  = lnMontox;
      END
      IF (ldFecVen BETWEEN CURRENT_DATE + 15 AND CURRENT_DATE + 30) THEN BEGIN
         ftcNumero4 = lcNumero;
         ftnMonto4  = lnMontox;
      END
      SUSPEND;
   END

END;

Como se trata de un stored procedure seleccionable lo ejecutamos así:

SELECT * FROM VENCIMIENTOS

Y este será el resultado que obtendremos:

FACTURAS3

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

Si no quieres ver los Null puedes usar la función COALESCE() para que desaparezcan.

Método 3. Usando una vista con una tabla CTE

Ya hemos visto en artículos anteriores lo útiles que pueden ser las tablas CTE, aquí también pueden ser usadas:

CREATE VIEW V_VENCIMIENTOS(
   FAC_FECVEN,
   HOY,
   FAC_MONTOX,
   SEMANA,
   FAC_MONTOX1,
   QUINCENA,
   FAC_MONTOX2,
   MES,
   FAC_MONTOX3)
AS

   WITH FacturasVencen AS (
      SELECT
         FAC_NUMERO,
         FAC_MONTOX,
         FAC_FECVEN
      FROM
         FACTURAS
   )

   SELECT
      F.FAC_FECVEN,
      F1.FAC_NUMERO AS "HOY",
      F1.FAC_MONTOX,
      F2.FAC_NUMERO AS "SEMANA",
      F2.FAC_MONTOX,
      F3.FAC_NUMERO AS "QUINCENA",
      F3.FAC_MONTOX,
      F4.FAC_NUMERO AS "MES",
      F4.FAC_MONTOX
   FROM
      FACTURAS F
   LEFT JOIN
      FacturasVencen F1
         ON F.FAC_NUMERO = F1.FAC_NUMERO AND
            F1.FAC_FECVEN = CURRENT_DATE
   LEFT JOIN
      FacturasVencen F2
         ON F.FAC_NUMERO = F2.FAC_NUMERO AND
            F2.FAC_FECVEN BETWEEN CURRENT_DATE + 1 AND CURRENT_DATE + 6
   LEFT JOIN
      FacturasVencen F3
         ON F.FAC_NUMERO = F3.FAC_NUMERO AND
            F3.FAC_FECVEN BETWEEN CURRENT_DATE + 7 AND CURRENT_DATE + 14
   LEFT JOIN
      FacturasVencen F4
         ON F.FAC_NUMERO = F4.FAC_NUMERO AND
            F4.FAC_FECVEN BETWEEN CURRENT_DATE + 15 AND CURRENT_DATE + 30
   WHERE
      F1.FAC_FECVEN IS NOT NULL OR
      F2.FAC_FECVEN IS NOT NULL OR
      F3.FAC_FECVEN IS NOT NULL OR
      F4.FAC_FECVEN IS NOT NULL;

Y al ejecutar a esa vista con este comando:

SELECT * FROM V_VENCIMIENTOS ORDER BY FAC_FECVEN

Este será el resultado que obtendremos:

FACTURAS4

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

Si no quieres ver los Null puedes usar la función COALESCE() para que desaparezcan.

Método 4. Usando una tabla en el FROM

También puedes ver las Facturas en filas con esta consulta:

SELECT
   *
FROM (
   SELECT
      CASE WHEN FAC_FECVEN = CURRENT_DATE THEN 'HOY'
           WHEN FAC_FECVEN BETWEEN CURRENT_DATE +  1 AND CURRENT_DATE +  6 THEN 'SEMANA'
           WHEN FAC_FECVEN BETWEEN CURRENT_DATE +  7 AND CURRENT_DATE + 14 THEN 'QUINCENA'
           WHEN FAC_FECVEN BETWEEN CURRENT_DATE + 15 AND CURRENT_DATE + 30 THEN 'MES'
      END AS FECHA_VENCIMIENTO,
      FAC_NUMERO,
      FAC_FECVEN,
      FAC_MONTOX
   FROM
      FACTURAS
   WHERE
      FAC_FECVEN BETWEEN CURRENT_DATE AND CURRENT_DATE + 30
 ) AS MiTabla
ORDER BY
   FAC_FECVEN

Y este será el resultado obtenido:

FACTURAS5

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

Conclusión:

Con todos los métodos obtenemos las Facturas que debemos cobrar pero la forma de salida de los resultados es diferente, a veces una de las salidas es la preferible. También en algunos métodos se escribe mucho más que en otros.

Artículo relacionado:

El índice del blog Firebird21

¿Por qué elegiste Firebird?

5 comentarios

Si eres un lector asiduo de este blog entonces muy probablemente estás usando Firebird.

¿Por qué, cuál fue el motivo de que hayas elegido Firebird?

Escribe tu respuesta en los comentarios.

Artículo relacionado:

El índice del blog Firebird21

 

Convirtiendo filas en columnas

Deja un comentario

Normalmente cuando escribimos un SELECT los resultados se muestran en una fila tras otra pero a veces podría interesarnos ver los resultados en una columna tras otra, o sea: convertir las filas en columnas.

Una forma de conseguirlo es con las tablas CTE (Common Table Expression) que como ya hemos visto en artículos anteriores son tablas virtuales.

Desde luego que para que esto sea manejable la cantidad de filas que convertiremos en columnas debe ser pequeña. Si nuestro SELECT nos devuelve miles o millones de filas no vamos a convertirlas a todas ellas en columnas, nadie siquiera miraría semejante monstruosidad y habríamos desperdiciado nuestro tiempo.

Ejemplo:

Tenemos una tabla de BANCOS que tiene 16 filas y queremos ver las primeras 4 filas como columnas.

COLUMNAS1

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

Para ello escribimos la siguiente consulta:

WITH MisBancos AS (
   SELECT
      BAN_CODSUC,
      BAN_IDENTI,
      BAN_NOMBRE
   FROM
      BANCOS
)

SELECT
   M.BAN_NOMBRE,
   N.BAN_NOMBRE,
   O.BAN_NOMBRE,
   P.BAN_NOMBRE
FROM
   BANCOS B
JOIN
   MISBANCOS M
      ON B.BAN_CODSUC = M.BAN_CODSUC AND
         B.BAN_IDENTI + 0 = M.BAN_IDENTI AND
         M.BAN_IDENTI = 1
JOIN
   MISBANCOS N
      ON B.BAN_CODSUC = N.BAN_CODSUC AND
         B.BAN_IDENTI + 1 = N.BAN_IDENTI AND
         N.BAN_IDENTI = 2
JOIN
   MISBANCOS O
      ON B.BAN_CODSUC = O.BAN_CODSUC AND
         B.BAN_IDENTI + 2 = O.BAN_IDENTI AND
         O.BAN_IDENTI = 3
JOIN
   MISBANCOS P
      ON B.BAN_CODSUC = P.BAN_CODSUC AND
         B.BAN_IDENTI + 3 = P.BAN_IDENTI AND
         P.BAN_IDENTI = 4
WHERE
   B.BAN_CODSUC = 0 AND
   B.BAN_IDENTI > 0
ROWS
 1

Y este es el resultado que obtenemos:

COLUMNAS2

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

Donde como puedes ver los nombres de los Bancos se muestran en columnas diferentes.

Otro método:

Podemos obtener el mismo resultado anterior usando una tabla que siempre tenga una fila y nunca más ni menos de una fila. Aquí usamos la tabla interna RDB$DATABASE porque está garantizada que siempre tendrá exactamente una fila pero tú puedes usar cualquier otra tabla si quieres.

WITH MisBancos AS (
   SELECT
      BAN_CODSUC,
      BAN_IDENTI,
      BAN_NOMBRE
   FROM
      BANCOS
)

SELECT
   M.BAN_NOMBRE,
   N.BAN_NOMBRE,
   O.BAN_NOMBRE,
   P.BAN_NOMBRE
FROM
   RDB$DATABASE
JOIN
   MISBANCOS M
      ON M.BAN_IDENTI = 1
JOIN
   MISBANCOS N
      ON N.BAN_IDENTI = 2
JOIN
   MISBANCOS O
      ON O.BAN_IDENTI = 3
JOIN
   MISBANCOS P
      ON P.BAN_IDENTI = 4

¿Cuál es el truco?

  1. Crear una tabla CTE
  2. Para cada columna que deseamos mostrar hacerle un JOIN a nuestra tabla principal con la tabla CTE. En ese JOIN establecer una relación entre ellas (en este ejemplo esa relación estaba dada por los valores de la columna BAN_IDENTI).

Artículos relacionados:

Usando CTE (Common Table Expression)

Otro ejemplo de CTE: ventas semanales

Usando una cross-table

¿Por qué usar una cross-table?

Usando varias CTE en una vista o en un stored procedure

El índice del blog Firebird21

Ejecutando un script desde Visual FoxPro

Deja un comentario

A veces podría ocurrir que necesitamos ejecutar los comandos DDL (los que crean tablas, índices, stored procedures, etc.) o DML (los que insertan, actualizan, borran, consultan datos) desde nuestra aplicación en Visual FoxPro.

La forma más fácil de conseguirlo es a través de un script. Como crear un script habíamos visto en este artículo:

https://firebird21.wordpress.com/2013/05/22/entendiendo-a-los-scripts/

Nuestro script debe consistir de:

  1. SET SQL DIALECT 3;     (si vamos a crear una Base de Datos)
  2. El comando CONNECT o el comando CREATE DATABASE     (si vamos a usar una Base de Datos que no está abierta. Si la Base de Datos ya está abierta estos comandos no serán necesarios)
  3. Los comandos que deseamos ejecutar
  4. El comando COMMIT o la instrucción SET AUTODDL ON

Para ejecutarlo, en la misma carpeta donde se encuentra nuestra aplicación (o en el PATH de búsqueda) debemos tener los archivos:

  • FBCLIENT.DLL
  • ISQL.EXE

Y ejecutamos al programa ISQL.EXE con las opciones -q (para que no muestre el mensaje “CONNECT or CREATE DATABASE …”, -i (para pedirle que ejecute un script) y el nombre del script.

Ejemplo:

Con el bloc de notas del Windows creamos el script “PRUEBA.SQL” que tiene este contenido:

CONNECT MIBASEDATOS USER SYSDBA PASSWORD masterkey;

CREATE TABLE PRUEBA (
   NUMERO INTEGER,
   NOMBRE VARCHAR(40),
   FECHA DATE
);

COMMIT;

INSERT INTO PRUEBA
        (NUMERO, NOMBRE, FECHA)
 VALUES (123 , 'HOLA', CURRENT_DATE);

COMMIT;

Y desde nuestro programa en Visual FoxPro lo ejecutamos de esta manera:

RUN ISQL -q -i "PRUEBA.SQL"

o de esta otra manera (la cual es preferible porque no mostrará el destello de la ventana negra “Símbolo del sistema”)

oShell = CreateObject("WScript.Shell")
oShell.Run("ISQL -q -i 'PRUEBA.SQL', 0, .T.")

Para que funcione, recuerda que debes tener a los archivos ISQL.EXE y FBCLIENT.DLL en la misma carpeta donde se encuentra tu aplicación o en el PATH de búsqueda.

Artículos relacionados:

Entendiendo a los scripts

Usando un script para insertar datos fijos

El índice del blog Firebird21

Evitando que se use un índice en una consulta

Deja un comentario

El optimizador de consultas de Firebird es muy bueno y en casi la totalidad de los casos elegirá la mejor alternativa: cual de los índices usar o no usar un índice.

Sin embargo no es perfecto y alguna vez podrías descubrir que está usando un índice cuando no debería hacerlo. Esto en general ocurre cuando el índice tiene poca selectivdad (es decir: muchos valores repetidos).

Puedes saber cual índice se usa en una consulta mirando el PLAN de la misma.

Si descubres que usando un índice obtienes los resultados a una cierta velocidad (por ejemplo: 1500 ms) y sin usarlo obtienes los resultados a una velocidad mayor (por ejemplo: 100 ms) lo que debes hacer es pedirle al Firebird que no use ese índice. Eso lo consigues agregándole a tu SELECT la cláusula PLAN.

Ejemplo:

Queremos obtener los nombres de los proveedores que empiezan con “H”. La tabla de PROVEEDORES tiene un índice según las columnas (PRO_CODSUC y PRO_NOMBRE).

En PRO_CODSUC se guarda el código de la Sucursal.

En PRO_NOMBRE se guarda el nombre del Proveedor.

SELECT
   PRO_NOMBRE
FROM
   PROVEEDORES
WHERE
   PRO_CODSUC = 0 AND
   PRO_NOMBRE LIKE 'H%'
ORDER BY
   PRO_CODSUC,
   PRO_NOMBRE

Verificamos el tiempo que tardó el SELECT en extraer los datos de la tabla de PROVEEDORES y encontramos que es de 1500 ms y que está usando el índice, así que ahora le pedimos que no use el índice:

SELECT
   PRO_NOMBRE
FROM
   PROVEEDORES
WHERE
   PRO_CODSUC = 0 AND
   PRO_NOMBRE LIKE 'H%'
PLAN
   (PROVEEDORES NATURAL)
ORDER BY
   PRO_CODSUC,
   PRO_NOMBRE

y encontramos que el tiempo disminuyó a solamente 100 ms.

Conclusión:

Aunque casi siempre el Firebird hace un muy buen trabajo eligiendo el índice más adecuado para una consulta no debes asumir que siempre será así, a veces puede equivocarse. Para descubrirlo siempre tienes que observar el tiempo de extracción de los datos y si es mayor que 500 ms entonces es casi seguro que el PLAN puede mejorarse. Un buen profesional siempre verifica los tiempos de extracción de los datos y los optimiza al máximo. Esto es de suma importancia cuando las consultas son ejecutadas frecuentemente y las tablas involucradas tienen muchas filas.

Artículos relacionados:

Usando un PLAN

Algo más sobre PLAN

El índice del blog Firebird21

Obteniendo el último valor

4 comentarios

Muchas veces puedes necesitar obtener el último valor de los registrados, por ejemplo:

  • ¿cuál fue el último precio de compra de este producto?
  • ¿cuándo fue la última vez que le cobramos a este cliente?
  • ¿cuándo fue la última vez que cambiamos el precio de venta de este producto?
  • ¿cuándo fue la última vez que vendimos este producto?
  • ¿cuándo fue la última vez que este profesor se ausentó?
  • ¿cuándo fue la última vez que pagamos este impuesto?
  • ¿cuánto pagamos por este impuesto la última vez?

Para responder a esas preguntas debes ordenar la consulta en forma descendente y obtener solamente la primera fila. Como la consulta estará ordenada en forma descendente, en la primera fila se encontrarán los datos más nuevos.

Ejemplo:

Queremos saber cuando fue la última vez que le cobramos al cliente que tiene el identificador 2457.

SELECT
   COB_FECHAX
FROM
   COBRANZAS
WHERE
   COB_IDECLI = 2457
ORDER BY
   COB_FECHAX DESC
ROWS
   1

COB_FECHAX es la columna donde se guardan las fechas de las cobranzas.

COB_IDECLI es la columna donde se guardan los identificadores de los clientes.

COBRANZAS es la tabla donde se guardan las cobranzas realizadas.

Si el resultado es NULL eso significará que nunca se le ha cobrado a este cliente. Si el resultado es distinto de NULL entonces esa será la fecha de la última cobranza.

Para que esta consulta sea muy rápida aunque tengas millones de registros en tu tabla de COBRANZAS deberás crear un índice descendente sobre la columna COB_FECHAX. Si tienes un índice ascendente ese no te servirá porque el Firebird no lo utilizará.

Artículo relacionado:

El índice del blog Firebird21

El manejo de los precios de costo y de venta

Deja un comentario

Cuando se realiza una venta son varios los precios que más adelante nos interesará conocer:

  • El precio por el cual se vendió el producto
  • El precio de lista, o sea el precio al cual se debería haber vendido el producto
  • El precio de costo, que a su vez puede ser:
    • Promedio ponderado
    • FIFO
    • LIFO

Todos estos precios deben estar registrados o debe ser posible calcularlos para mostrarlos en los informes. Por ejemplo un informe de costos de ventas nos dirá el precio de venta del producto, el precio de costo, la diferencia en la moneda utilizada, y el porcentaje de ganancia. Eso implica que deberemos conocer el precio de venta y el precio de costo de cada producto en cada venta. Ejemplo: Precio de costo = 140, precio de venta = 200, diferencia = 60, porcentaje de ganancia = 42,86%

¿Cómo hacemos la registración?

Tenemos varias posibilidades:

  1. En nuestra tabla DETALLES_MOVIMIENTOS_VENTAS guardamos el precio de venta, el precio de lista, el precio de costo
  2. En una tabla PRECIOS_LISTA guardamos los precios a los cuales se deberían haber vendido los productos. Populamos esa tabla a través de un trigger que le inserta una fila cuando el precio de venta es distinto al precio de lista. En una tabla PRECIOS_COSTO guardamos los precios de costo de los productos, según el método que la empresa utiliza (Promedio ponderado, FIFO, LIFO) o mejor aún: guardamos los tres precios de costo porque eso hará más atrayente a nuestra aplicación. Los precios de costo solamente cambian cuando se realiza una compra o una devolución de productos al proveedor, no cambian cuando se realizan ventas u ocurren otras salidas (por robo, incendio, etc.)
  3. En una tabla CAMBIOS_PRECIOS_VENTA registramos los datos de cada cambio en el precio de venta de un producto (el identificador del producto, la fecha en que cambió su precio de venta, el nuevo precio de venta). En una tabla CAMBIOS_PRECIOS_COSTO registramos todos los datos de cada cambio en el precio de costo de un producto (el identificador del producto, la fecha en la cual cambió su precio de costo, el nuevo precio de costo).

¿Cuál es la mejor alternativa?

Eso ya depende de las circunstancias. La alternativa 1 es la que requiere de menos trabajo pero también es la que nos dará menor información. La alternativa 2 es la que menos espacio ocupará en el disco duro pero no siempre podremos saber cual era el precio de lista de un producto a una determinada fecha (por ejemplo: el precio de lista era de 100, después subió a 110, después subió a 120. Como no se hizo ni una venta de ese producto cuando su precio era de 110, no podremos saber que alguna vez ése fue su precio de venta). La alternativa 3 es la que requiere de más trabajo, las consultas serán un poco más lentas, pero en contrapartida será la que nos dará más información.

En una aplicación de alta gama deberíamos usar la alternativa 3 porque es la que sin dudas preferirán los gerentes, y como en ese caso dispondremos de servidores y de computadoras muy rápidas no habrá problemas con la velocidad.

Artículo relacionado:

El índice del blog Firebird21

Older Entries