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.

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 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.

275_03

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.

275_04

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:

275_05

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:

275_06

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.

275_07

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.

275_08

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

275_09

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

275_10

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.

275_11

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.

275_12

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.

275_13

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.

275_14

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.

275_15

15. Pegue y ejecute la sentencia en el Browser.

275_16

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).

275_17

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.

275_18

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.

275_19

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.

275_20

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.

275_21

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.

275_22   

 Revíselo en su Browser.

275_23

 

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.

275_24

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.

275_25

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

275_26

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.

275_27

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.

275_28

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.

275_29

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

27. Revise la información con el Browser.

275_30

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.

275_31

30. Nuevamente revise en el Browser.

275_32

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.

 

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

Comentarios

0 comentarios

Inicie sesión para dejar un comentario.