Generador de sentencias SQL

Proscai cuenta con herramientas para facilitar el análisis de la información de tu base de datos y para que la conviertas en oportunidades de negocio, apoyando el desarrollo y la productividad de tu empresa.

El Generador de cubos te permite de forma muy sencilla estructurar sentencias de SQL sin necesidad de grandes conocimientos, para crear consultas y generar Cubos con el fin de operar de forma flexible, potente y veloz la información de interés de tu base de datos. Esta una herramienta para el uso de personal con conocimientos básicos de SQL, pero también para programadores experimentados.

La base de Proscai archiva los datos en tablas separadas que se encuentran conectadas por relaciones definidas que hacen posible combinar datos de diferentes tablas sobre pedido, en la cual solo es necesario identificar con claridad la necesidad y el conocimiento de las tablas en las cuales se encuentran los datos que brindarán la información solicitada.

Contenido del artículo:

  - Estructura de la base de datos para manejar sentencias SQL y query

        Funciones básicas para generar sentencias SQL

        Principales tablas de Proscai

  - Cubos y consultas en Proscai

        Utilizar el Generador de cubos

 
Estructura de la base de datos para manejar sentencias SQL y query

Las bases de datos son sistemas de trabajo organizado que permiten catalogar y clasificar información, utilizando tablas que el sistema utiliza para ubicar rápidamente cualquier información guardada en ellas. Es necesario identificar en qué tabla se encuentran los campos, y conocer qué tablas requieren una de mayor detalle para poder extraer la información.

Para iniciar conoceremos la estructura lógica de almacenamiento que usa la base de datos, es semejante a un archivo de papel que se localiza por ejemplo en un edificio, piso, pasillo, ubicación, ficha y que de este modo es posible recuperar la información que interesa de un modo ágil, gracias a los índices y la estructura organizada del archivo. La estructura de la base de datos está conformada por Tablas (Main file), que contienen Registros (filas), compuesta por Campos donde se encuentran los datos.

http://www.proscai.com/kbi/nt/275_01.jpg

En el ejemplo cada registro contiene información de un cliente. Observe que la tabla almacena en este caso 3 registros con 7 campos. El nombre de cada campo viene dado por la fila de encabezado. Cada registro contiene una secuencia que lo identifica y uno o más campos llave.

275_02

En resumen: Una base de datos contendrá tablas, que a su vez contienen registros y en estos se encontrarán los datos distribuidos en una serie de campos. Cada registro de la tabla guarda la información particular de una unidad o miembro de un mismo grupo.
La instrucción para hacer una selección de datos, de una o más tablas contenidas en una misma base de datos, se denomina SENTENCIA, para generar cubos será necesario que la sentencia incluya dimensiones y métricas.

  • Las DIMENSIONES son los nombres de los elementos que conforman la tabla para manejar los datos: parámetros y familias, tablas y datos, las cuales contendrán a su vez un conjunto de elementos, por ejemplo en la dimensión Producto, cada uno de sus productos será un elemento.
  • Las MÉTRICAS son elementos que servirán para comparar los datos, son aquellos rangos que se pueden medir: Importe bruto, Importe Neto, Costos, Piezas, Ofertado, etc.

Para construir una consulta SQL des conveniente hacernos como mínimo tres preguntas básicas:

  • ¿Qué datos nos están pidiendo?
    por ejemplo: el Código y Nombre de los clientes.
  • ¿Dónde se encuentran esos datos?
    están en la tabla Clientes.
  • ¿Qué requisitos deben cumplir los registros?
    por ejemplo: quienes tienen un saldo mayor o igual a 4000 pesos

Sintaxis SQL

 Ejemplo  select  CODIGO, NOMBRE
 from  CLIENTES
 where SALDO ACTUAL  >= 4000
 Sintaxis SQL  select CAMPOS(separados por comas)
 from TABLA
 where CONDICIÓN
 Para Proscai  select CLICOD,CLINOM
 from FCLI
 where CLISACT >=4000

 

También es posible extraer información de tablas vinculadas a la que en ese momento sea la de mayor detalle, para combinarlas en un reporte o consulta.

  • ¿Qué datos nos están pidiendo? Clientes y sus documentos con saldo.
  • ¿Dónde se encuentran esos datos? en la tabla Documentos
  • ¿Qué requisitos deben cumplir los registros?
 Ejemplo select  CODIGO , NOMBRE,  NUMERO DE DOCUMENTO Y SALDO
from  FDOC
 Sintaxis SQL select CAMPOS
from TABLA
 Para Proscai Select  CLICOD AS CODIGO, CLINOM AS NOMBRE, DNUM AS NUMERO_DE_DOCUMENTO, DCANT AS SALDO
From FDOC
left join FCLI on FCLI.CLISEQ=FDOC.CLISEQ

 

Funciones básicas para generar sentencias SQL

Función Modo de empleo
SELECT
En la cláusula SELECT se establecen las listas de campos que mostrará el resultado de la consulta.
¿Qué datos quiero extraer?
Código de cliente: CLICOD, y Número de documento: DNUM
  select CLICOD, CLINOM
FROM
En la cláusula FROM se establece la fuente de los datos, las tablas que intervienen en la consulta.
¿Dónde están los datos?
Determinar la tabla de detalle (según la estructura de la BD) que contenga los campos a extraer, para emplearla como tabla principal.
Tabla principal: FCLI: CLIENTES
  from FCLI 
WHERE
Las condiciones establecidas en la cláusula WHERE tienen como propósito filtrar registros de la tabla. De hecho es donde se establece el filtro de registros, es decir, qué registros serán considerados para mostrar los datos y cuáles no.
¿Qué requisitos deben cumplir los registros?
Condición que debe cumplirse para la extracción de los datos:
  =    Igual que
  >=  Mayor o igual
  <=  Menor o igual
  <>   Diferente de
AND 
OR
   
  where CLISACT >=4000

LEFT JOIN
Función que será utilizada en caso de existir más de una tabla.

Permite realizar una "consulta combinada" en la cual se busca en la tabla superior la liga con la tabla principal.
  left join FCLI ON FCLI.CLISEQ=FDOC.CLISEQ
GROUP BY
En la cláusula GROUP BY se establecen los grupos de datos por los que se quiere obtener totales.
¿Cómo deben agruparse los datos?
Especifica la agrupación que se da a los datos.
Se usa siempre en combinación con funciones de totalización (SUMCOUNTMAX…).
ORDER BY
La cláusula ORDER BY permite finalmente ordenar el resultado por diferentes columnas, y en sentido ascendente o descendente, antes de ser presentado.
¿Cómo deben ordenarse los datos resultantes?
Presenta el resultado ordenado por los campos indicados. 
El orden puede expresarse con ASC (ascendente) y DESC (descendente). El valor predeterminado es ASC.

 

Es importante resaltar que si deseas extraer información de varias tablas debes respetar las ligas de vinculación, esto te permitirá generar una consulta piramidal.

 

Principales tablas de Proscai

Nombre de tabla Proscai Descripción de la tabla
FCLI Catálogo de Clientes
FPRV Catálogo de Proveedores
FINV Catálogo de Inventarios por producto
FDOC Documentos (Recepciones / Facturas-Tickets)
FAXINV Líneas de documento o Auxiliar de inventarios
FAX Auxiliar de Clientes y Proveedores (importe)
FPENC Encabezados Pedidos y Órdenes de compra
FPLIN Líneas de pedidos u órdenes de compra
FBENC Catálogo de cuentas
FPOLIZA Encabezados Póliza
FBANMOV Movimientos de Bancos
FAG Parámetros
FALMCAT Catálogo de almacenes
FFAM Familias
FUNIDAD Unidad de medida
FCAJAS Rollos y números de serie
FALM Detalle de almacenes
FENS Ensambles de producción

 

Si desea consultar los nombre de los principales campos en Proscai, revise el tema Nombre de tablas y campos en Proscai.

 

Cubos y consultas en Proscai

Utilizar el Generador de cubos

El Generador de cubos una herramienta que te ayudará a crear cubos y consultas sin necesidad de grandes conocimientos de SQL y te facilitará la elaboración de sentencias SQL a través de un manejo más sencillo y dinámico de la base de datos.

1. Selecciona del menú Proscai el módulo SIG. Del menú Importa / Exporta selecciona la opción Genera cubos (1).

El sistema presenta la ventana Generador de cubos que trae precargadas las principales Tablas de Proscai y de cada una, los campos más importantes.

A continuación te describimos la ventana:

Conforme a la estructura de la base de datos de Proscai se han agrupado en 4 bloques las tablas necesarias para consultas específicas de acuerdo con su uso:

FDOC: incluye las tablas correspondientes a clientes y cuentas por cobrar, proveedores y cuentas por pagar, así como los documentos que los afectan y cada una de las partidas contenidas en ellos.
  FAG, FALMCAT, FFAM, FUNIDAD,FCLI,FPRV, FDOC, FINV, FAX, FAXINV

FPED: Incluye las tablas de pedidos, órdenes de compra, órdenes de producción y cotizaciones, así como su relación con los catálogos de clientes, proveedores y productos.
  FCLI, FINV, FPENC, FPLIN

FINV: Incluye las tablas de los catálogos de productos, almacenes, ensambles y la información relacionada con ellos.
  FCAJAS, FPROV, FUNIDAD, FINV, FALM,FENS

CONT: Incluye las tablas que contienen el catálogo de cuentas, los movimientos de cada una de ellas y los asientos contables generados.
  FBENC, FPRV, FPOLIZA, FBANMOV, FAX

En la ventana de trabajo encontrarás el campo FILTR que permite registrar símbolos que sirven como condicionantes para la extracción de los datos, los principales filtros son:

En el campo GROUP podrás agregar un valor para agrupar la información por cualquiera de las dimensiones seleccionadas, según los datos que solicites; y a través del campo ORDER podrás ordenar la información por uno o más campos.

La casilla de verificación SELEC permite deshabilitar campos necesarios para el filtro, pero que no desees que se vean reflejados.

Si deseas borrar una línea, selecciónala y haz clic en el botón Borrar, para borrar todos los elementos de la ventana haz clic simultáneamente en la tecla Shift + botón Borrar

A partir de las 3 preguntas claves que comentamos al inicio de este artículo, podrás realizar múltiples consultas. Para iniciar vamos a generar una consulta básica para ver el saldo de los clientes.

  • ¿Qué datos nos están pidiendo? Saldo actual de los clientes.
  • ¿Dónde se encuentran esos datos? En la tabla Clientes.
  • ¿Qué requisitos deben cumplir los registros? Ningún requisito.

2. Haz clic en el botón FDOC (1) y selecciona la tabla FCLI (2).
Observa que aparece un listado de campos en la parte inferior de la ventana (3).

3. Haz doble clic en el campo CLICOD, sobre el campo CLINOM y sobre el campo CLISACT (1), estos se irán agregando en la ventana (2). 

4. Haz clic en el botón Make Select (3).

Se agrega la sentencia en la parte inferior (1), y además la guarda en memoria para que puedas validarla en MySQL Workbench.

5. Abre MySQL Workbench, pega la sentencia que traes en memoria (1) y ejecútala (2).

Observa el listado (3), también muestra varios clientes cuyo saldo es 0 o muy bajo, razón por la cual vamos a registrar un filtro y que sólo muestre los saldos mayores a $10,000 pesos.

6. Regrese a la ventana Generador de cubos y haz clic en el botón Cambia (1), selecciona la línea CLISACT (2) y en el campo  FILTR registra la condición >=10000 (igual o mayor a 10,000 pesos) (3). Haz clic en el botón OK para que se registre la condición (4). 

7.  Haz clic en el botón Make Select (5). Observa que agrega la condicionante del saldo a la sentencia (6).

8. En MySQL Workbench pega la nueva sentencia (1) y ejecútela (2).

La lista de clientes disminuye considerablemente para este ejemplo (3).

Ahora vamos a indicar el orden en que deseamos ver los datos.

9. Nuevamente ve a la ventana Generador de cubos y haz clic en el botón Cambia (1), selecciona la línea CLISACT (2) y en el campo Order registra el valor 1 (3) y haz clic en el botón OK (4). 

Se agrega el valor en la columna Order (5) y con esto indicamos que queremos ver la información en orden ascendente con referencia al saldo.

10. Haz clic en el botón Make Select (6) para crear la sentencia (7).

11. Pega y ejecuta la sentencia en MySQL Workbench.

Los datos aparecen ya en el orden indicado.

Ahora vamos a manejar información de DOS TABLAS y utilizaremos la herramienta SELECT.

12. Siguiendo con el mismo ejemplo, de la tabla FDOC (1) selecciona con doble clic los campos DNUM (Número de documento) y DCANT (Saldo) (2). Haz clic en el botón Make Select (3).

Esto va a presentar todos los documentos de cada cliente, de los ya filtrados cuyo saldo total era mayor o igual a 10,000.

13. Al revisar en MySQL Workbench se observa que aparecen muchos documentos con saldo en ceros y se repite el saldo actual en cada línea.

Ahora se aplicarán unos filtros para que sólo aparezcan de los clientes filtrados anteriormente, los documentos con saldo mayor a $5,000 pesos. 

14. Selecciona el campo DCANT (1) y a través del botón Cambia registra el filtro para que sólo muestre los documentos iguales o mayores a $5,000.00 pesos (2). Con esto eliminaremos los documentos pequeños.

15. Ahora selecciona el campo CLISACT Saldo actual (1) y desmarca la casilla Select (2), haz clic en el botón OK (3).

Al desmarcar casilla Select se le está indicando que lo considere (para nuestro ejemplo es que filtre solo los clientes cuyo saldo total es mayor a 10,000 pesos y los ordene) pero que no lo muestre especificado como tal.

16. Observa que automáticamente se registra ahora el valor 0 para el campo DCANT. Haz clic en el botón Make Select (1) para generar la sentencia.

17. Al revisar en MySQL Workbench la sentencia obtenida, observamos solo los documentos de los clientes con saldo actual.

18. Regresa a Proscai y elimina todos los campos de la ventana Generador de cubos.

 

Otra característica es que algunas tablas presentan campos con * que despliegan información directamente relacionada con el campo seleccionado, pero que pertenecen a otra tabla.

1. Haz clic en el botón de la tabla FINV (1), elige la tabla FINV (2) y agrega con doble clic los campos ICOD e IDESCR (3).

2. Elige la tabla FALM Almacenes (4) y agrega el campo ALMCANT Existencia (5), selecciona ALMNUM -*Número de bodega (6) y oprime simultáneamente la tecla Ctrl + doble clic sobre el campo ALMNUM, de esta forma agregará en automático la descripción del almacén que pertenece a la tabla FALMCAT (7).

3. Oprime el botón Make Select (8).

 4. Revisa el resultado en MySQL Workbench.

5. Regresa a Proscai y elimina todos los campos de la ventana Generador de cubos.

 

Existe otra forma en que se vinculan las tablas y es a través de campos específicos de diferentes tablas que mantienen una relación.

Para este ejemplo utilizaremos de la tabla FINV el campo IPRVde la tabla FPRV el campo PRVCOD.

1. De la tabla FINV (1) agrega los campos ICOD e IDESCR (2), elige también el campo IPRV que contiene * (3) y con la tecla Ctrl + doble clic en el campo agrégalo, de esta manera podremos ver los productos por proveedor. Observa que el campo IPRV (Código del proveedor) pertenece a la tabla FINV y muestra la descripción desde la tabla FPRV (4). 

Si además queremos agregar algunos datos de contacto con el proveedor, por ejemplo teléfono y nombre del contacto, puedes combinar las tablas de la siguiente manera.

2. De la tabla FPRV (1) agrega los campos que requieras, para este ejemplo Teléfono y Contacto (2) y genera la sentencia (3).   

3. Prueba la sentencia en MySQL Workbench y observa que muestra los datos solicitados.

 

Al relacionar diferentes tablas, el sistema te podría enviar un mensaje cuando no existe conexión entre ellas.

Si manejas Color y Talla existe la posibilidad de llamar los diferentes elementos que componen al producto: el código del producto específico (hijo), el producto raíz, el color y la talla.

1. Selecciona la tabla FINV (1). 

Observa que el campo ICOD no tiene *, sin embargo desplegará el detalle del producto (2).

2. Simultáneamente presiona la tecla Ctrl más doble clic el campo ICOD para agregarlo (3).

Observa que de forma automática despliega el detalle del producto. 

 

Cuando se utiliza la tabla FDOC Documentos (1) existe la posibilidad de filtrar los documentos a analizar, ya sean de CxC o CxP.

1. De la tabla FCLI (2) agrega los campos CLICOD y CLINOM (3).

2. De la tabla FDOC (4) agrega los campos DNUM y DCANT (5), al utilizar el campo DESCXC (6) si su valor es 1 mostrará los documentos contenidos en el módulo Facturación y cuando su valor sea 2 mostrará los documentos del módulo Recepciones. 

De forma automática registra el valor de filtro como 1 (7), genera la sentencia a través del botón Make Select (8). 

Nota: Para trabajar con Proveedores, deberás cambiar manualmente el valor a 2.

3. Revise la información en MySQL Workbench.

Ahora vamos a agrupar la información por cliente.

4. Nuevamente en la ventana Generador de cubos, seleccione el campo CLICOD agregado (1).

5. En el campo de filtro GROUP agrega el valor 1 (2) y genera la sentencia a través del botón Make Select (3).

6. Nuevamente revisa el resultado en MySQL Workbench.

Se observa que agrupó por cliente totalizando el saldo, dejando como referencia uno de los documentos del cliente.

Las posibilidades que te ofrece esta ventana son muy amplias y te puede simplificar tu trabajo.

 

¿Fue útil este artículo?
Usuarios a los que les pareció útil: 4 de 7

Comentarios

0 comentarios

Inicie sesión para dejar un comentario.