Generador de sentencias SQL

Proscai continúa integrando herramientas que le faciliten el análisis de su información para que la convierta en oportunidades de negocio, apoyando el desarrollo y la productividad de su empresa.

En esta ocasión nos referimos al Generador de cubos, que le permite de forma muy sencilla estructurar sentencias (sin necesidad de grandes conocimientos de SQL) para crear Consultas y emitir sentencias para generar Cubos, con el fin de recuperar de forma flexible, potente y veloz la información de interés de su base de datos.

Su base de datos 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 tener identificada claramente la necesidad y el conocimiento de las tablas en las cuales se encuentran los datos que brindarán la información solicitada.

Una herramienta para el uso de personal con conocimientos básicos de SQL, pero también usada por programadores experimentados.

CONTENIDO:

 - 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 nos permiten catalogar y clasificar información, utilizando tablas que el sistema utiliza para ubicar rápidamente cualquier información guardada en ellas en un momento determinado. Es necesario identificar en qué tabla se encuentran los campos, y además qué tablas requieren una de mayor detalle para poder extraer la información.

Para iniciar, conocerá la estructura lógica de almacenamiento que usa su base de datos: a semejanza de 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.

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.

En resumen: Una BD contendrá tablas que a su vez contendrán 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 sus 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 debemos hacernos como mínimo tres preguntas:

Primero hemos de preguntarnos: ¿qué datos nos están pidiendo?      por ejemplo: el Código y Nombre de los clientes.   Lo siguiente que nos preguntamos es: ¿dónde están esos datos?      están en la tabla Clientes.   Y por último: ¿qué requisitos deben cumplir los registros?      por ejemplo: quienes tienen un saldo mayor o igual a 4000 pesos.

Sintaxis SQL

Ejemplo	Sintaxis SQL	Para Proscai
select  CODIGO , NOMBRE  from  CLIENTES  where SALDO ACTUAL  >= 4000	select CAMPOS(separados por comas)   from TABLA   where CONDICIÓN	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 están esos datos? en la tabla Documentos   ¿qué requisitos deben cumplir los registros?

 

Ejemplo	Sintaxis SQL	Para Proscai
select  CODIGO , NOMBRE,  NUMERO DE DOCUMENTO Y SALDO from  FDOC	select CAMPOS  from TABLA	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, 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, que registros serán considerados para mostrar sus 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 que exista más de una tabla, es decir una "consulta combinada" en la cual se busca en la tabla superior la liga con la tabla principal  leftjoin FCLI ON FCLI.CLISEQ=FDOC.CLISEQ
GROUP BY   En la cláusula GROUP BY se establece 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 (SUM, COUNT, MAX…).
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 desea extraer información de varias tablas, debe respetar las ligas de vinculación, esto le permitirá generar una consulta piramidal.

  

Principales tablas de Proscai
 

Nombre de tabla en 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 90 Nombre de tablas y campos en Proscai.

 

Cubos y consultas en Proscai

Utilizar el Generador de cubos

Proscai cuenta con una herramienta que le ayudará a generar cubos y consultas sin necesidad de grandes conocimientos de SQL, a través de su ventana Generador de cubos, que le facilita la elaboración de sentencias SQL a través de un manejo más sencillo y dinámico de la base de datos

1. Seleccione del menú Proscai, el módulo SIG.

2. Del menú Importa / Exporta, seleccione el comando Genera cubos.

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.

De acuerdo 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 a 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, 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á el campo FILT que le permite registrar símbolos que puede utilizar como condicionantes para la extracción de los datos, los principales filtros son:

En el campo GROUP podrá agregar un valor para agrupar su información, por cualquiera de las dimensiones que seleccione, según los datos que esté solicitando.

A través del campo ORDER podrá ordenar su información por uno o más campos.

La casilla de verificación SELEC le permite deshabilitar campos necesarios para el filtro, pero que no desee que se vean reflejados.
 
Si desea borrar una línea, selecciónela y haga clic en el botón Borrar, si desea borrar todos los elementos de la ventana, haga clic simultaneamente en la tecla Shif (Mayúsculas) + botón Borrar.

A partir de las 3 preguntas claves podrá realizar múltiples consultas. Para iniciar va a generar una consulta básica en donde vea el saldo de los clientes.

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

3. Haga clic en el botón FDOC y seleccione la tabla FCLI.
Observe que aparece un listado de campos en la parte inferior de la ventana.

 4. Haga doble clic en el campo CLICOD.
5. Ahora haga doble clic sobre el campo CLINOM. Haga doble clic en el campo CLISACT.

6. Haga clic en el botón Make Select.

Esta acción además de generar la sentencia la guarda en memoria para que pueda validarla en My SQL Query Browser.

7. Pegue y ejecute la sentencia creada en My SQL Query Browser.

Al revisar la sentencia, se observa que es correcta, pero también muestra varios clientes cuyo saldo es 0 o muy bajo, razón por la cual se va a registrar un filtro, para que sólo muestre los saldos mayores a 10,000 pesos.

8. Regrese a la ventana Generador de cubos.

9. Haga clic en el botón Cambia, seleccione la línea CLISACT y en el campo FILT registre la condición >=10000 (igual o mayor a 10,000 pesos)

10. Haga clic en el botón OK para que se registre la condición. Haga clic en el botón Make Select.

Observe que agrega la condicionante del saldo.

11. Nuevamente pegue la sentencia y ejecútela en My SQL Query Browser.

La lista de clientes disminuye considerablemente para este ejemplo.

Ahora va a indicar el orden en que desea se muestren los datos.

12. Nuevamente vaya a la ventana Generador de cubos.

13. Haga clic en el botón Cambia, seleccione la línea CLISACT y en el campo Order registre el número 1.

Con esto se está indicando que quiere ver la información en orden ascendente con referencia al saldo.

14. Haga clic en el botón Make Select para crear la sentencia SQL.

15. Pegue y ejecute la sentencia en el Browser.

Aparecen ya en el orden indicado.

Ahora va a manejar información de DOS TABLAS y además utilizará la herramienta SELECT.

16. Siguiendo con el mismo ejemplo, de la tabla FDOC seleccione los campos DNUM (Número de documento) y DCANT (Saldo).

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

Al revisar en el Browser, 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 1000 pesos.

17. Seleccione el campo DCANT y registre el filtro para que sólo muestre los documentos iguales o mayores a 1000.00 pesos.

Con esto eliminaremos los documentos pequeños.

18. Seleccione el campo CLISACT (Saldo actual), haga clic en el botón Cambio y desmarque la casilla SELECT, ahora haga clic en el botón OK.

Al desmarcar un campo 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.

Observe que automaticamente se registra ahora el valor 0 para el campo DCANT.

19. Haga clic en el botón MAKE SELECT para generar la sentencia y revísela en el Browser.

Ahora aparecen solo los documentos de los clientes con saldo actual.

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

Por ejemplo en el grupo de tablas FINV, en la tabla FALM (Almacenes), al se seleccionar ALMNUM (Número de bodega) y al hacer Ctrl + docle clic sobre el campo ALMNUM, incluye en automático la descripción del almacen, que pertenece a la tabla FALMCAT.

   

 Revíselo en su Browser.

 

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

Para este ejemplo utilizaremos los campos IPRV de la tabla FINV, con el campo PRVCOD de la tabla FPRV.

20. De la tabla FINV agregue los campos ICOD, IDESCR e IPRV que contiene * (Ctrl + doble clic en el campo). De esta manera queremos ver los productos por proveedor.

En este ejemplo ya se pueden ver los productos por proveedor.

Si deseara agregar algunos datos para comunicarse con el proveedor, por ejemplo teléfono y nombre del contacto, puede combinar las tablas de la siguiente manera.

21. Seleccione el campo IPRV del centro de la ventana y busque ahora la tabla FPRV, selecciónela.

22. De la tabla FPRO agregue los campos que requiera, para este ejemplo teléfono y contacto.  

23. Genere la sentencia y pruébela en el Browser.

Si usted quisiera relacionar directamente ambas tablas, el sistema le envía un mensaje de que no existe conexión entre tablas.

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

24. Seleccione la tabla FINV.

25. Seleccione el campo ICOD, observe que no tiene *, sin embargo le desplegará el detalle del producto.

26. Seleccione la tecla Ctrl y simultáneamente haga doble clic sobre el campo ICOD.

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

Cuando utilice la tabla FDOC (Documentos), existe la posibilidad de filtrar los documentos que va a analizar, ya sean de CxC o CxP.

Al utilizar el campo DESCXC, si su valor es 1 mostrará los documentos contenidos en el módulo de 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.

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

27. Revise la información con el Browser.

Ahora va a agrupar esta información por cliente.

28. Nuevamente en la ventana Generador de cubos, seleccione el campo CLICOD que agregó.

29. En el filtro GROUP agregue el valor 1 y genere la sentencia.

30. Nuevamente revise en el Browser.

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

Cómo puede observar, las posibilidades que le ofrece esta ventana son muy amplias y le simplifica grandemente el trabajo.