Explosion Lista de materiales usando "WITH CTE"

[anon33106502]

Hola compañeros necesito de los experto en SQL.
Debo explosionar una lista de materiales y para he logrado casi entender el uso del “With con CTE”,

Un CTE es un algo que nos deja en memoria el resultado de una consulta, para que lo podamos llamar luego dentro de esa misma consulta. Es como una “variable” temporal que nos evita tener que declarar una vista no persistente, o una tabla temporal.

La sintaxis es muy sencilla:

WITH <nombre de su CTE> <nombre de columnas> 
AS 
( 
<query de origen> 
) 
SELECT * FROM <nombre de su CTE>

El queery de abajo funciona bien, pero quisiera que me ayudaran a presentar el resultado de forma ordenada pues es una lista de materiales, es decir que cada papa tenga debajo de el a sus hijitos con el stock, lo solicitado y lo comprometido.

– WITH Common_table_expression

WITH BOM (Code, Level, TreeType, Nombre, Quantity, Stock, Solicitado, Comprometido) AS
(

– Anchor member definition

SELECT
    T0.Code, 0 as Level, T2.TreeType, T2.ItemName, T0.Qauntity,T3.OnHand, T3.OnOrder,T3.IsCommited 
  FROM
    dbo.OITT T0 
       inner join OITM T2 on T0.Code = T2.ItemCode
       inner join OITW T3 on T2.ItemCode = T3.ItemCode
  WHERE    
    T0.Code = 'AC-343.I 020' and T3.WhsCode='AP'

UNION ALL
– Recursive member definition

  SELECT    
    T1.Code , Level +1 , T2.TreeType, T2.ItemName, T1.Quantity, T3.OnHand, T3.OnOrder,T3.IsCommited 
       FROM        
    dbo.ITT1 AS T1 
       inner join OITM T2 on T1.Code = T2.ItemCode
       inner join OITW T3 on T2.ItemCode = T3.ItemCode
    JOIN BOM ON T1.Father = BOM.Code
       Where T3.WhsCode='AG'
)

– Statement that executes the CTE
SELECT * FROM BOM OPTION (MAXRECURSION 5)

[andresramirez]

Hola Alvarez
Si la funcion WITH no te entrega lo que necesitas, queda el camino de siempre, de crear una tabla temporal y mediante un procedimiento almacenado realizar su llenado y finalmente generar un reporte.

[anon33106502]

es lo que queria evitar, lo complejo de estas creando tablas temporales y usar store procedure

[anon33106502]

Hola Andres ramirez finalmente cree una tabla temporal y use un store procedure, pero incluyendo la consulta con With Comm Table Expresion. la idea es obtener la de la una lista de materiales la disponibilidad en diferentes almacenes de los componentes de esa lista que son de compra, es decir que tiene el TreeType= ‘N’
aqui debajo les comparto como quedo el SP,
en SAP hay un pequeñp query que le solicta al usuario el parametro
Declare @Code as nvarchar (50)
SET @Code= (SELECT T0.[Code] FROM OITT T0 WHERE T0.[Code] =[%1])
EXECUTE Sp_Disponibilidad_BOM @Code
– =============================================
– Author: <Eric Alvarez, Consultors IBIZ >
– Create date: <24-Julio_2018>
– Description:
– =============================================
CREATE PROCEDURE Sp_Disponibilidad_BOM
@CODE AS NVARCHAR(50)
AS
BEGIN
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’tbl_ITDM’) AND type in (N’U’))
DROP TABLE [dbo].[tbl_ITDM]

create Table tbl_ITDM ( ItemCode varchar(50),
ItemName varchar (100),
CardCode varchar(15),
CardName varchar(100),
Country varchar(3),
TreeType varchar (1),
Phantom varchar(1),
InvntryUom varchar(100),
PrcrmntMtd varchar (1),
Stock_AG NUMERIC(19,6),
Stock_AP NUMERIC(19,6),
Stock_PT NUMERIC(19,6),
Comprometido_AG NUMERIC(19,6),
Comprometido_AP NUMERIC(19,6),
Comprometido_PT NUMERIC(19,6),
Solicitado_AG NUMERIC(19,6),
Solicitado_AP NUMERIC(19,6),
Solicitado_PT NUMERIC(19,6) )

Insert Into tbl_ITDM
SELECT T0.[ItemCode], T0.[ItemName], T0.CardCode, T2.[CardName], T2.[Country], T0.TreeType, T0.Phantom, T0.InvntryUom, T0.[PrcrmntMtd],

T1.[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-PT’ and SQ.[ItemCode] = T0.[ItemCode]) ‘Stock_PT’,

T1.[IsCommited] ‘Comprometido_AG’,
(SELECT SQ.[IsCommited] FROM OITW SQ Where SQ.[WhsCode] =‘AP’ and SQ.[ItemCode] = T0.[ItemCode]) ‘Comprometido_AP’,
(SELECT SQ.[IsCommited] FROM OITW SQ Where SQ.[WhsCode] =‘B-PT’ and SQ.[ItemCode] = T0.[ItemCode]) ‘Comprometido_PT’,

T1.[OnOrder] ‘Solicitado_AG’,
(SELECT SQ.[OnOrder] FROM OITW SQ Where SQ.[WhsCode] =‘AP’ and SQ.[ItemCode] = T0.[ItemCode]) ‘Solicitado_AP’,
(SELECT SQ.[OnOrder] FROM OITW SQ Where SQ.[WhsCode] =‘B-PT’ and SQ.[ItemCode] = T0.[ItemCode]) ‘Solicitado_PT’

FROM OITM T0
LEFT JOIN OITW T1 ON T0.[ItemCode] = T1.[ItemCode]
LEFT JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode]
WHERE T1.[WhsCode] =‘AG’ and T0.[validFor]=‘Y’ ;

WITH BOM (Code, Level, TreeType, ItemName, Provedor, QTYRequerida, Stock_AG, Stock_AP, Stock_PT, Stock_Total, Comprometido_AG, Comprometido_AP, Total_Comprometido, Solicitado_AG) AS
(
– Anchor member definition
SELECT
T0.Code, 0 as Level, T2.TreeType, T2.ItemName, T2. CardName, T0.Qauntity, T2.Stock_AG, T2.Stock_AP, T2.Stock_PT, (T2.Stock_AG+T2.Stock_AP+ T2.Stock_PT),
T2.Comprometido_AG, T2.Comprometido_AP, (T2.Comprometido_AG + T2.Comprometido_AP), Solicitado_AG
FROM
dbo.OITT T0
inner join tbl_ITDM T2 ON T0.Code= T2.itemCode
WHERE
T0.Code = @CODE

UNION ALL
– Recursive member definition
SELECT
T1.Code , Level +1 , T3.TreeType, T3.ItemName, T3.CardName, T1.Quantity, T3.Stock_AG, T3.Stock_AP, T3.Stock_PT, (T3.Stock_AG+T3.Stock_AP+T3.Stock_PT),
T3.Comprometido_AG, T3.Comprometido_AP, (T3.Comprometido_AG + T3.Comprometido_AP), T3.Solicitado_AG
FROM
dbo.ITT1 AS T1
inner join tbl_ITDM T3 ON T1.Code = T3.ItemCode
JOIN BOM ON T1.Father = BOM.Code
Where LEVEL <= 5

)
– Statement that executes the CTE
SELECT Code, ItemName, Provedor , SUM(QTYRequerida) , Stock_AG, Stock_AP, Stock_PT, Stock_Total, Comprometido_AG, Comprometido_AP, Total_Comprometido, Solicitado_AG
FROM BOM
Where TreeType=‘N’
Group By Code, ItemName, Provedor, Stock_AG, Stock_AP, Stock_PT, Stock_Total, Comprometido_AG, Comprometido_AP, Total_Comprometido, Solicitado_AG
ORDER BY Code

END
GO

[andresramirez]

Alvarez, te recomiendo asignarle el tipo temporal a la tabla que estas manejando (prefijo #), es una buena practica que a nivel de base de datos tiene sus ventajas.
CREATE TABLE #tablaTemporal

[anon33106502]

Hola Andres,
Agradezco tu sugerencia, voy probar a usar las tablas temporales