Qery ricorsive tramite CTE Common Table Expression

venerdì 11 dicembre 2015 - 13.01

Elenco Threads

Stanze Forum

Aggiungi ai Preferiti

Cerca nel forum

dompa72 Profilo | Senior Member

245 messaggi | Data Invio: ven 11 dic 2015 - 13:01

Ciao a tutti e sempre un piacere leggere questo forum dove si trovano tante soluzioni
Ho un problema per me complesso ma per alcuni di voi banalissimo
Devo valorizzare una Distinta Base multilivello

Utilizzo questo Batch come esempio

USE [tempdb]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'dbo.BOM') AND type in (N'U'))
DROP TABLE dbo.BOM;

CREATE TABLE [dbo].[BOM](
[PN] [varchar](10) NULL,
[CodArt] [varchar](10) NULL,
[Coeff] [float] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[BOM] ([PN], [CodArt], [Coeff]) VALUES (N'PN1', N'Art1', 1);
INSERT [dbo].[BOM] ([PN], [CodArt], [Coeff]) VALUES (N'PN1', N'Art2', 2);
INSERT [dbo].[BOM] ([PN], [CodArt], [Coeff]) VALUES (N'PN1', N'Art3', 1);
INSERT [dbo].[BOM] ([PN], [CodArt], [Coeff]) VALUES (N'PN1', N'SL1', 2);
INSERT [dbo].[BOM] ([PN], [CodArt], [Coeff]) VALUES (N'SL1', N'Art4', 1);
INSERT [dbo].[BOM] ([PN], [CodArt], [Coeff]) VALUES (N'SL1', N'Art5', 1);
INSERT [dbo].[BOM] ([PN], [CodArt], [Coeff]) VALUES (N'SL1', N'SL2', 1);
INSERT [dbo].[BOM] ([PN], [CodArt], [Coeff]) VALUES (N'SL2', N'Art6', 1);
INSERT [dbo].[BOM] ([PN], [CodArt], [Coeff]) VALUES (N'PN2', N'Art1', 1);
INSERT [dbo].[BOM] ([PN], [CodArt], [Coeff]) VALUES (N'PN2', N'Art8', 2);
INSERT [dbo].[BOM] ([PN], [CodArt], [Coeff]) VALUES (N'PN2', N'Art3', 1);
INSERT [dbo].[BOM] ([PN], [CodArt], [Coeff]) VALUES (N'PN2', N'SL1', 1);
INSERT [dbo].[BOM] ([PN], [CodArt], [Coeff]) VALUES (N'PN2', N'SL3', 1);
INSERT [dbo].[BOM] ([PN], [CodArt], [Coeff]) VALUES (N'SL3', N'Art7', 1);

--Visualizza Dati
Select * from BOM;

--CTE
WITH CTE(PN, PN2, Coeff, CodArt, Livello) AS
(
SELECT T1.PN, t2.PN PN2, T1.Coeff, T1.CodArt, 0
FROM BOM as T1
left join(Select distinct PN from BOM) as T2 on T1.CodArt = T2.PN

UNION ALL

SELECT T.PN, a.PN PN2, (a.Coeff * t.Coeff) as Coef, a.CodArt, Livello + 1
FROM BOM AS a
INNER JOIN CTE AS t
ON a.PN = t.CodArt
)
SELECT *
FROM CTE
ORDER BY PN, PN2, Livello;

-----------------------------------------------------
Questo il Primo Output
PN CodArt Coeff
PN1 Art1 1
PN1 Art2 2
PN1 Art3 1
PN1 SL1 2
SL1 Art4 1
SL1 Art5 1
SL1 SL2 1
SL2 Art6 1
PN2 Art1 1
PN2 Art8 2
PN2 Art3 1
PN2 SL1 1
PN2 SL3 1
SL3 Art7 1

Questo il Risultato della CTE

PN PN2 Coeff CodArt Livello
PN1 NULL 1 Art1 0
PN1 NULL 2 Art2 0
PN1 NULL 1 Art3 0
PN1 SL1 2 SL1 0
PN1 SL1 2 Art4 1
PN1 SL1 2 Art5 1
PN1 SL1 2 SL2 1
PN1 SL2 2 Art6 2
PN2 NULL 1 Art1 0
PN2 NULL 2 Art8 0
PN2 NULL 1 Art3 0
PN2 SL1 1 SL1 0
PN2 SL1 1 Art4 1
PN2 SL1 1 Art5 1
PN2 SL1 1 SL2 1
PN2 SL2 1 Art6 2
PN2 SL3 1 SL3 0
PN2 SL3 1 Art7 1
SL1 NULL 1 Art4 0
SL1 NULL 1 Art5 0
SL1 SL2 1 SL2 0
SL1 SL2 1 Art6 1
SL2 NULL 1 Art6 0
SL3 NULL 1 Art7 0

No riesco ad eliminare la riga che richiama la ricosione (quella che ha generato la riorsione) o il Passaggio di livello, queste righe:
PN1 SL1 2 SL1 0
PN1 SL1 2 SL2 1
PN2 SL1 1 SL1 0
PN2 SL1 1 SL2 1
PN2 SL3 1 SL3 0
SL1 SL2 1 SL2 0

C'è un modo per fare questo?

Grazie
Domenico

Ho risolto modificando la seconda query in questo modo passando per una variabile tabella:

Declare @Table_PN as Table (PN varchar(10));
Insert Into @Table_PN (PN) (Select distinct PN from BOM);

WITH CTE(PN, PN2, Coeff, CodArt, Livello) AS
(
SELECT T1.PN, t2.PN PN2, T1.Coeff, T1.CodArt, 0
FROM BOM as T1
left join @Table_PN T2 on T1.CodArt = T2.PN

UNION ALL

SELECT T.PN, a.PN PN2, (a.Coeff * t.Coeff) as Coef, a.CodArt, Livello + 1
FROM BOM AS a
INNER JOIN CTE AS t
ON a.PN = t.CodArt
)
SELECT CTE.PN, CTE.PN2, CTE.Coeff, CTE.CodArt, CTE.Livello
FROM CTE Left Join @Table_PN tp on CTE.CodArt = tp.PN
where tp.PN is null
ORDER BY CTE.PN, PN2, Livello;

avendo questo risultato

PN PN2 Coeff CodArt Livello
PN1 NULL 1 Art1 0
PN1 NULL 2 Art2 0
PN1 NULL 1 Art3 0
PN1 SL1 6 Art4 1
PN1 SL1 8 Art5 1
PN1 SL2 20 Art6 2
PN2 NULL 1 Art1 0
PN2 NULL 2 Art8 0
PN2 NULL 1 Art3 0
PN2 SL1 3 Art4 1
PN2 SL1 4 Art5 1
PN2 SL2 10 Art6 2
PN2 SL3 1 Art7 1
SL1 NULL 3 Art4 0
SL1 NULL 4 Art5 0
SL1 SL2 10 Art6 1
SL2 NULL 10 Art6 0
SL3 NULL 1 Art7 0

Spero che ci sia un modo migliore

Grazie

renarig Profilo | Expert

517 messaggi | Data Invio: dom 13 dic 2015 - 19:34

>Spero che ci sia un modo migliore

Non so se migliore o peggiore, ma certamente diverso
non usa CTE ma usa una TabellaTemporanea ( T2 )

confesso pero che non ho capito bene il significato di PN e PN2
ma mi sembra che il risultato sia corrispondente al tuo

 Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra

dompa72 Profilo | Senior Member

245 messaggi | Data Invio: lun 14 dic 2015 - 16:45

Grazie per l'aiuto

E' valido anche il tuo metodo

Torna su

Stanze Forum

Elenco Threads

Partecipa anche tu! Registrati!

Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !