Alguna vez se ha preguntado como sacar esa consulta SQL que le han pedido?, intentas proyectar tablas con FULL JOIN, INNER JOIN, LEFT JOIN y todavia no se obtienen los resultados deseados...
A veces hay que recurrir a las subconsultas para llegar a nuestro objetivo...
Quizás es típico el estar buscando las formas de ejecutar consultas SELECT-SQL para obtener un conjunto de datos específico, esto porque ya estás cansado de crear cursores hechos con CREATE CURSOR y rellenados mediante un ciclo SCAN ... ENDSCAN (ok, podrías hacerlo con DO WHILE NOT EOF(), pero eso ya está pasado de moda, además de que resulta ser poco eficiente debido a que no utilizarás a su máximo la optimización RushMore, una de las ventajas del uso de Visual FoxPro). Haz consultado con algunos de tus colegas y te responden que efectivamente, debe haber alguna manera de hacerlo por medio de sentencias SQL. Y tu sexto sentido casi te lo puede gritar!!
A continuación expondremos un caso típico en los cuales será necesario hacer unos cuantos "trucos" para obtener el tan ansiado cursor que no nos deja en paz:
La gerencia te pide que expongas en un reporte un resumen de las compras y ventas de los artículos en el último mes, donde compares lado a lado, cuantos se vendieron, cuantos se compraron por artículo, quedando quizas algo por el estilo:
| ID Articulo | Descripcion | Compras | Ventas |
| 001 | Camisa | XXX | XXX |
| 002 | Pantalon | XXX | XXX |
| 003 | Blusa | XXX | XXX |
Parece algo sencillo y no muy difícil de realizar, pero es tipico que en el diseño de tu base de datos hayas dejado por separado una entidad para compras y una entidad para ventas, por lo que tendriamos los siguientes entidades:
Lo primero que se nos podría ocurrir es una consulta donde proyectaramos "todos" vs "todos" para obtener conjunto de datos deseados:
SELECT Ventas.IDArticulo, SUM(compras.Cantidad) as SumaCompras,;
SUM(ventas.cantidad) AS SumaVentas ;
FROM Compras ;
FULL JOIN Ventas ON Ventas.IdArticulo = Compras.IdArticulo ;
GROUP BY Ventas.idarticulo ;
INTO CURSOR cResumen
A primera vista parece buena la idea, sumamos todas las cantidades de compras, todas las cantidades de ventas de una mezcla de todos los registros, no? Pero sabras que pasa?: No funciona, ya que obtendremos datos que nada tienen que ver, primeramente porque se estan cualificando las tuplas de la tabla ventas contra las tuplas de la tabla compras, pero nótese que hay algunos IDs que están en una, pero que no están en la otra, dandonos el siguiente resultado:
Podras comentar, "Ya casi", solo me faltó el codigo 002 que es de la tabla compras que no estaba en la tabla ventas, hmmm si, en efecto es asi, puedes seguir intentando cambiar las clausulas por LEFT JOIN, RIGTH JOIN, cambiar Ventas.Articulo = Compras.IdArticulo por Compras.IdArticulo = Ventas.Articulo en lo correspondiente a la cláusula ON (de los operadores de proyeccion), pero obtendrás diferentes resultados que seguirán sin ser los que esperabas.
Inclusive, puedes llegar a pensar que tal vez el operador de igualdad sale sobrando, e intentas una más para ver si funciona:
SELECT ventas.idArticulo,;
SUM(ventas.cantidad) AS TotalVentas,;
SUM(compras.cantidad) AS TotalCompras ;
FROM ventas,compras ;
GROUP BY ventas.idArticulo ;
INTO CURSOR cReporte
Y tenemos el siguiente resultado:
Que sucedió?, lamentablemente al hacerlo de esta forma se está utilizando implicitamente un operador de igualdad entre las entidades (INNER JOIN), y eso sin tomar en cuenta que la suma no son lo que deberia tener, por lo que tampoco obtenemos lo que queremos.
Entonces? Qué es lo que debemos hacer?, la solución es sencilla, el uso de subconsultas. Resulta ser que el algebra de conjunto (que es la teoría que sustenta a la práctica de el lenguaje SQL) tiene un pequeño "truco" para normalizar este pequeño desperfecto en lo que parece no tener solución. Esto se le llaman subconsultas (mas adelante veremos que otros nombres se les conoce).
En el parrafo anterior hice mención a un tema que es base de esto, la normalización, en vez de intentar proyectar las columnas que serán calculadas (en nuestro ejemplo se sumaron, pero bien pudieron haber sido contadas) para llegar al resultado inmediato, debemos crear un conjunto de datos intermedio, el cual, nos servirá para ahora si proyectarlo correctamente:
SELECT idArticulo, 000000 as nCompras, Cantidad as nVentas ;
FROM Ventas ;
UNION ;
SELECT idArticulo, Cantidad as nCompras , 000000 as nVentas ;
FROM Compras ;
INTO CURSOR cResumen
Con el query anterior obtendremos un conjunto de datos normalizados como el que sigue:
Ya se va viendo mejor?, claro!, ahora a éste conjunto de datos sólo le faltaría hacer una agrupación y suma, para que quede como lo deseamos:
SELECT idArticulo, SUM(nCompras) as TotalCompras ,;
SUM(nVentas) as TotalVentas ;
FROM cResumen ;
GROUP BY idArticulo ;
INTO CURSOR cReporte
Quedando como sigue:
Ahora si, llegamos a los resultados deseados (puedes hacer la suma manual, no me he equivocado :-), como comentaba en el inicio del artículo a veces es *NECESARIO*. Cabe destacar que en las consultas he obviado las clásulas WHERE, ya que es demasiado obvio que siempre deberán incluirse en cada una de la consultas que hemos unido (con la claúsula UNION).
Aprovecho este momento de tu atención para demostrar la manera en que también podrá ser realizado con la VFP9 (la próxima versión del producto), en la que se puede simplificar aún más esta sentencia SQL:
SELECT codigo,;
SUM(cResumen.nCompras) AS TotalCompras ,;
SUM(cResumen.nVentas ) AS TotalVentas ;
FROM (SELECT codigo, ;
CAST(0 as Int) AS nCompras, Cantidas as nVentas ;
FROM Ventas ;
UNION ;
SELECT Codigo,;
Cantidad as nCompras, CAST(0 as Int) AS nVentas ;
FROM Compras ) cResumen ;
GROUP BY cResumen.Codigo ;
INTO CURSOR cReporte
Aquí se hará uso de lo que se le denomina sentencias anidadas, en donde tenemos varias partes clave.
En primer lugar se puede apreciar que se está realizando una sentencia anidad en al utilizar como cursor de procedencia ... otra consulta!! FROM ( SELECT ... ), esto nos dá la ventaja de poder anidar tantas consultas sean necesarias.
Como segundo punto a favor estamos utilizando una de las nuevas funciones: CAST(), con ella prescindiremos de los trucos que hemos estado usando para "forzar" que las columnas calculadas de VFP tengan un tipo de datos, ancho o precisión que se requiera, en vez de tener que utilizar por ejemplo 000000 para forzar que fuera un entero de ancho 6, o usar $0 para que sea de tipo Moneda, y un sin fin de etceteras.
Con la combinación de ambas, obtenemos una consulta mas compacta, te podrán preguntar ¿En qué nos beneficia eso?, en que dicha consulta, si en dado momento desea pasarse a un servidor de base de datos (por ejemplo MS-SQLServer), la transición sea más ligera, ya que desde hace mucho tiempo estos manejan consultas anidadas y funciones de conversión explícita.
Quizás no debemos de dejar pasar el hecho que a veces puede ser contraproducente el tener todo en un una sola consulta, ya que todo dependerá de cómo estén formados tus datos, asi por ejemplo, pudiera ser necesario que separes en varias consultas para darle mayor interactividad a tu aplicación, por ejemplo:
WAIT WINDOW "Buscando Ventas en el periodo... Espere" NOWAIT
SELECT idArticulo, 000000 as nCompras, Cantidad as nVentas ;
FROM Ventas ;
WHERE BETWEEN(Fecha, ldInicio, ldFinal) ;
INTO CURSOR cVentas ;
WAIT WINDOW "Buscando Compras en el periodo... Espere" NOWAIT
SELECT idArticulo, Cantidad as nVentas,000000 as nCompras ;
FROM Compras ;
WHERE BETWEEN(Fecha, ldInicio, ldFinal) ;
INTO CURSOR cCompras ;
WAIT WINDOW "Realizando resumen de Compra ventas... Espere" NOWAIT
SELECT * FROM cCompras ;
UNION ;
SELECT * FROM cVentas ;
INTO CURSOR cResumen
Asi pues, entre más pasos intermedios dejes, puedes enviar más mensajes a tu usuario indicándo qué algo se está haciendo, ya que en cuanto el número de datos empiece a crecer puede ser una posibilidad a que se vaya tardando cada vez más, por ejemplo, si lo pruebas con 1000 datos... será rapidísimo, pero en cuanto llegues a los millones de registros, se tardará, y muchos usuarios sienten que dos o tres segundos es un mundo, y mientras se tarda los puedes animar un poquito, así no se desesperarán y empiecen a "tronar" tu aplicación porque "ya no respondía" ... El cuánto las separes o unas te lo dejo a tu criterio, ya que siempre dependerá de tu caso específico.
Sea cual sea la solución tomada, debemos resumir lo siguiente:
Es un error común el pensar que todo se puede hacer con una sola consulta SELECT-SQL, a veces hay que utilizar subconsultas (o consultas anidadas), y no solo proyecciones directas.
Espero que este pequeño tutorial les sea de utilidad.
Espartaco Palma Martínez
