La sentencia JOIN para combinar el resultado de varias consultas.
En ocasiones el cliente nos solicita cierta información que es el resultado de crear varias consultas de selección a diferentes tablas del sistema. Pero toda esa información se debe presentar junta, es decir, se debe mostrar como un listado de todos registros de dos o más consultas que cumplen cierta condición.
Por ejemplo recientemente me pidieron un listado de los faltantes de producción, donde aparecieran los ítems con faltantes, el proveedor por defecto, el comprador, la cantidad requerida en las Ordenes de Fabricación, la disponibilidad del stock para varios almacenes (OnHand, IsCommited, OnOrder), y además la cantidad solicitada en la orden de producción y la semana en que la orden de producción se terminaba. Tal y como aparece en la imagen 1.
Imagen 1. Resultado del Query final.
Para lograr esta presentación de los registros utilice la sentencia JOIN (unir, combinar) de SQL la cual permite combinar no solo los registros de una o más tablas, sino también el resultado de dos o más consultas de SQL a través de un campo común en todas.
El objetivo de este artículo es presentarles un ejemplo de cómo realice el JOIN de varias consultas sobre diferentes tablas de SAP Business One, para intercambiar opiniones y escuchar sus recomendaciones.
Iniciemos con la presentación de la consulta y luego analizaremos sus partes:
Reporte de Faltantes en las órdenes de Producción Vs Disponibilidad de stock, indicando la semana de finalización de la Orden de Fabricación
SELECT
stock.ItemCode,
stock.ItemName,
stock.CardCode, Stock.Proveedor,
stock.SlpName 'Nombre Comprador',
Ordenes.Requerido 'Requerido OF',
Stock.Stock_AG + stock.Stock_AP + stock.Stock_MTY as 'Stock Total',
Stock.Stock_AG + stock.Stock_AP + stock.Stock_MTY - Ordenes.Requerido as 'Faltante Prod',
stock.Comprometido_AG + stock.Comprometido_AP 'Total Comprometido',
stock.Solicitado_AG, Semanas.[Semana 33],semanas.[Semana 34],Semanas.[Semana 35], Semanas.[Semana 36],semanas.[Semana 37],Semanas.[Semana 38], Semanas.[Semana 39],
[Semana 40],semanas.[Semana 41],Semanas.[Semana 42], Semanas.[Semana 43],semanas.[Semana 44],Semanas.[Semana 45], Semanas.[Semana 46],semanas.[Semana 47],Semanas.[Semana 48], Semanas.[Semana 49]
[Semana 50], semanas.[Semana 51],Semanas.[Semana 52]
FROM
-- Primer query, muestra la disponibilidad de stock (stock de 3 almacenes, lo comprometido en los almacenes AP y AG y lo solicitado en el almacén AG)
( SELECT
T0.[ItemCode],
Cast (T1.[ItemName] as varchar(45)) ItemName,
T2.CardCode,
Cast (T2.CardName as varchar(35)) Proveedor,
T3.SlpName,
T0.[OnHand] 'Stock_AG',
(SELECT SQ.[OnHand] FROM OITW SQ Where SQ.[WhsCode] ='AP' and SQ.[ItemCode]=T0.[ItemCode]) 'Stock_AP',
(SELECT SQ.[OnHand] FROM OITW SQ Where SQ.[WhsCode] ='B-MTY' and SQ.[ItemCode]=T0.[ItemCode]) 'Stock_MTY',
T0.[IsCommited] 'Comprometido_AG',
(SELECT SQ.[IsCommited] FROM OITW SQ Where SQ.[WhsCode] ='AP' and SQ.[ItemCode]=T0.[ItemCode]) 'Comprometido_AP',
T0.[OnOrder] 'Solicitado_AG'
FROM OITW T0
INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode]
INNER JOIN OCRD T2 ON T1.CardCode = T2.CardCode
INNER JOIN OSLP T3 ON T2.SlpCode=T3.SlpCode
WHERE T0.[WhsCode]='AG' and T1.[TreeType] ='N'
) as Stock
INNER JOIN
-- Segundo Query, trae la cantidad requerida en las ordenes de fabricación abiertas y planificadas.
-- Se usan las tablas OWOR y WOR1
-- La cantidad requerida se obtiene como la diferencia entre (`T1.[PlannedQty] - T1.[IssuedQty]`)
(
SELECT
T2.[ItemCode],
SUM (T1.[PlannedQty] - T1.[IssuedQty]) Requerido
FROM OWOR T0
INNER JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN OITM T2 ON T1.[ItemCode]= T2.[ItemCode]
WHERE T0.[Status] IN ('P','R') and T2.[TreeType] ='N' and T2.[InvntItem] ='Y'
GROUP BY T2.[ItemCode], T2.[ItemName]
) as Ordenes ON stock.ItemCode= ordenes.ItemCode
INNER JOIN
-- Tercer Query, muestra las cantidades requeridas en las órdenes de fabricación por semana en la
-- que vence o se termina la orden de fabricación. Se utiliza la función PIVOT, para mostrar en
-- columnas las cantidades requeridas, según la fecha de vencimiento [DueDate] de la orden de fabricación.
(
Select
A.[Código],
CASE WHEN [33] IS NOT NULL THEN [33] ELSE 0 END As'Semana 33', CASE WHEN [34] IS NOT NULL THEN [34] ELSE 0 END As'Semana 34',
CASE WHEN [35] IS NOT NULL THEN [35] ELSE 0 END As'Semana 35', CASE WHEN [36] IS NOT NULL THEN [36] ELSE 0 END As'Semana 36',
CASE WHEN [37] IS NOT NULL THEN [37] ELSE 0 END As'Semana 37', CASE WHEN [38] IS NOT NULL THEN [38] ELSE 0 END As'Semana 38',
CASE WHEN [39] IS NOT NULL THEN [39] ELSE 0 END As'Semana 39', CASE WHEN [40] IS NOT NULL THEN [40] ELSE 0 END As'Semana 40',
CASE WHEN [41] IS NOT NULL THEN [41] ELSE 0 END As'Semana 41', CASE WHEN [42] IS NOT NULL THEN [42] ELSE 0 END As'Semana 42',
CASE WHEN [43] IS NOT NULL THEN [43] ELSE 0 END As'Semana 43', CASE WHEN [44] IS NOT NULL THEN [44] ELSE 0 END As'Semana 44',
CASE WHEN [45] IS NOT NULL THEN [45] ELSE 0 END As'Semana 45', CASE WHEN [46] IS NOT NULL THEN [46] ELSE 0 END As'Semana 46',
CASE WHEN [47] IS NOT NULL THEN [47] ELSE 0 END As'Semana 47', CASE WHEN [48] IS NOT NULL THEN [48] ELSE 0 END As'Semana 48',
CASE WHEN [49] IS NOT NULL THEN [49] ELSE 0 END As'Semana 49', CASE WHEN [50] IS NOT NULL THEN [50] ELSE 0 END As'Semana 50',
CASE WHEN [51] IS NOT NULL THEN [51] ELSE 0 END As'Semana 51', CASE WHEN [52] IS NOT NULL THEN [52] ELSE 0 END As'Semana 52'
FROM
(
SELECT
T2.[ItemCode] 'Código',
SUM (T1.[PlannedQty] - T1.[IssuedQty]) Requerido,
datepart (wk,T0.[DueDate]) semana
FROM OWOR T0
INNER JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN OITM T2 ON T1.[ItemCode]= T2.[ItemCode]
WHERE T0.[Status] IN ('P','R') and T2.[TreeType] ='N' and T2.[InvntItem] ='Y'
GROUP BY T2.[ItemCode], T2.[ItemName], T0.[DueDate]
) A
PIVOT (SUM (Requerido) FOR [Semana] IN ([33],[34],[35],[36],[37],[38],[39],[40],[41],[42], [43],[44],[45],[46],[47],[48],[49],[50],[51],[52]) )A
) As Semanas ON Stock.ItemCode =semanas.Código
WHERE Stock.Stock_AG + stock.Stock_AP + stock.Stock_MTY - Ordenes.Requerido <0
ORDER By 1
La consulta anterior se puede resumir de forma breve de la siguiente forma:
SELECT stock.campos1, requerido.campos2, semanas.campos3
FROM
(
SELECT campos1 FROM tablas1
) AS Stock
Inner Join
(
SELECT campos2 FROM tablas2
) As Ordenes ON stock.ItemCode= ordenes.ItemCode
Inner Join
(
SELECT campos3 FROM tablas3
) AS semanas ON Stock.ItemCode = semanas.Código
WHERE condicion
ORDER BY 1
Ahora analicemos a detalle el Query sin perder de vista el requerimiento del cliente.
En nuestro caso se definió que los faltantes en las órdenes de producción serian la diferencia entre el stock_Total y la suma de la cantidad requerida de un ítem en todas las órdenes de fabricación planificadas y liberadas, es decir quedó así:
(Stock_Total – Requerido) as 'Faltante Prod'.
Dónde:
- Stock_Total: es la suma del stock en tres almacenes (AG, AP y MTY),
- Requerido (Cantidad requerida) es igual a la suma de la cantidad planificada menos la cantidad consumida en las ordenes de producción
SUM (T1.[PlannedQty] - T1.[IssuedQty]).
El stock Total, lo comprometido y lo solicitado para cada Ítems se obtuvo con el primer Query sobre las tablas de inventario OITW y OITM. Se usaron subconsultas para obtener datos de otros almacenes.
SELECT
T0.[ItemCode],
Cast (T1.[ItemName] as varchar(45)) ItemName,
T2.CardCode,
Cast (T2.CardName as varchar(35)) Proveedor,
T3.SlpName,
T0.[OnHand] 'Stock_AG',
(SELECT SQ.[OnHand] FROM OITW SQ Where SQ.[WhsCode] ='AP' and SQ.[ItemCode] = T0.[ItemCode]) 'Stock_AP',
(SELECT SQ.[OnHand] FROM OITW SQ Where SQ.[WhsCode] ='B-MTY' and SQ.[ItemCode] = T0.[ItemCode]) 'Stock_MTY',
T0.[IsCommited] 'Comprometido_AG',
(SELECT SQ.[IsCommited] FROM OITW SQ Where SQ.[WhsCode] ='AP' and SQ.[ItemCode] = T0.[ItemCode]) 'Comprometido_AP',
T0.[OnOrder] 'Solicitado_AG'
FROM OITW T0
INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode]
INNER JOIN OCRD T2 ON T1.CardCode = T2.CardCode
INNER JOIN OSLP T3 ON T2.SlpCode=T3.SlpCode
WHERE T0.[WhsCode]='AG' and T1.[TreeType] ='N'
La cantidad requerida se obtuvo con una consulta a las tablas OWOR y WOR1, donde lo requerido es igual a la suma de la cantidad planificada menos la cantidad consumida SUM (T1.[PlannedQty] - T1.[IssuedQty]).
SELECT
T2.[ItemCode],
SUM (T1.[PlannedQty] - T1.[IssuedQty]) Requerido
FROM OWOR T0
INNER JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN OITM T2 ON T1.[ItemCode]= T2.[ItemCode]
WHERE T0.[Status] IN ('P','R') and T2.[TreeType] ='N' and T2.[InvntItem] ='Y'
GROUP BY T2.[ItemCode], T2.[ItemName]
Y por último, el tercer Query muestra por semanas las cantidades requeridas en las órdenes de fabricación. Aquí utilizamos la función PIVOT, para llevar a columnas las cantidades requeridas según la fecha de vencimiento de la orden de fabricación.
Realmente quede asombrado con el resultado final de unir varias consultas. Lo más interesante es que este Query se puede ejecutar perfectamente desde SAP Business One y no se pierden las flechas de enlace a los datos maestros de artículos y proveedores. Antes los perdía cuando hacia los Store Procedure.
Espero les guste esta experiencia de lo que se puede lograr con el SQL Server. Quedo atento a sus comentarios y sugerencias para seguir aprendiendo.
