Con più domande
SQL 2005 - 2000. Query di aggiornamento con funzioni di aggregazione

Domanda
-
Ho 3 tabelle: TabellaA, TabellaB e TabellaC. Semplificando in base al problema, le tabelle sono così strutturate:
TabellaA: IdOrdineProduzione|DataConsegnaOrdine
TabellaB: IdOrdineProduzione|IdDoc|IdRiga
TabellaC: IdDoc, IdRiga e DataConsegna
In pratica la TabellaB fa da JOIN tra TabellaA e TabellaC
Il mio obiettivo è aggiornare il campo DataConsegnaOrdine di TabellaA, con la minore tra le DataConsegna della TabellaC, ovviamente sfruttando i legami tra IdOrdineProduzione, IdDoc e IdRiga.
Come posso fare?
C'è da tenere conto inoltre che se tra le varie date ce n'è una a null, la data in TabellaA deve rimanere NULL. Usando la funzione MIN, una data non compilata viene presa come valore minore?
Spero di aver spiegato bene il problema.
Io ho scrit query, ma credo che mi possa andar bene anche una stored procedure. Deve girare sia su sql2000 che sql2005.
Grazie a tutti.
- Modificato patapatty mercoledì 14 settembre 2011 10:51
Tutte le risposte
-
Il mio obiettivo è aggiornare il campo DataConsegnaOrdine di TabellaA, con la minore tra le DataConsegna della TabellaC, ovviamente sfruttando i legami tra IdOrdineProduzione, IdDoc e IdRiga.
Come posso fare?
Ciao,
Prova a dare un'occhiata al seguente esempio:
USE tempdb; CREATE TABLE dbo.A( IdOrdineProduzione int NOT NULL, DataConsegnaOrdine datetime NULL, CONSTRAINT PK_A PRIMARY KEY(IdOrdineProduzione) ); CREATE TABLE dbo.C( IdDoc int NOT NULL, IdRiga int NOT NULL, DataConsegna datetime NULL, CONSTRAINT PK_C PRIMARY KEY(IdDoc, IdRiga) ); CREATE TABLE dbo.B( IdOrdineProduzione int NOT NULL, IdDoc int NOT NULL, IdRiga int NOT NULL, CONSTRAINT PK_B PRIMARY KEY(IdOrdineProduzione, IdDoc, IdRiga), CONSTRAINT FK_BA FOREIGN KEY(IdOrdineProduzione) REFERENCES dbo.A(IdOrdineProduzione), CONSTRAINT FK_BC FOREIGN KEY(IdDoc, IdRiga) REFERENCES dbo.C(IdDoc, IdRiga) ); INSERT dbo.A(IdOrdineProduzione) VALUES (1), (2), (3); INSERT dbo.C VALUES (10, 1, '2011-01-01'), (10, 2, '2011-02-02'), (10, 3, '2011-03-03') , (20, 1, '2011-04-04'), (20, 2, NULL) , (30, 1, '2011-05-05'); INSERT dbo.B VALUES (1, 10, 1), (1, 10, 2), (1, 10, 3) , (2, 20, 1), (2, 20, 2) , (3, 30, 1); UPDATE A SET DataConsegnaOrdine = Q.DataConsegna FROM dbo.A JOIN ( SELECT B.IdOrdineProduzione , MIN(COALESCE(C.DataConsegna, '1753-01-01')) AS DataConsegna FROM dbo.B JOIN dbo.C ON B.IdDoc = C.IdDoc AND B.IdRiga = C.IdRiga GROUP BY B.IdOrdineProduzione ) AS Q ON A.IdOrdineProduzione = Q.IdOrdineProduzione WHERE Q.DataConsegna <> '1753-01-01'; SELECT * FROM dbo.A; /* Output: IdOrdineProduzione DataConsegnaOrdine ------------------ ----------------------- 1 2011-01-01 00:00:00.000 2 NULL 3 2011-05-05 00:00:00.000 (3 row(s) affected) */ DROP TABLE dbo.B, dbo.A, dbo.C;
In sostanza la tabella derivata calcola la DataConsegna minima per ogni IdOrdineProduzione e nel caso sia NULL assegna il valore minimo per il data type datetime. Il comando di UPDATE aggiorna la colonna DataConsegnaOrdine della tabella A prendendo in esame le righe corrispondenti la cui DataConsegna sia diversa dal valore minimo (ovvero NULL).Ciao!
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo
http://social.technet.microsoft.com/Forums/it-IT/sqlserverit -
Ciao,
se non devi per forza usare funzioni di aggregazione, puoi usare una SELECT TOP 1 ... ordinata per DataConsegna.
Guarda questo esempio in cui ho preso in prestito parte del codice postato da Lorenzo :-)USE tempdb; CREATE TABLE dbo.A( IdOrdineProduzione int NOT NULL, DataConsegnaOrdine datetime NULL, CONSTRAINT PK_A PRIMARY KEY(IdOrdineProduzione) ); CREATE TABLE dbo.C( IdDoc int NOT NULL, IdRiga int NOT NULL, DataConsegna datetime NULL, CONSTRAINT PK_C PRIMARY KEY(IdDoc, IdRiga) ); CREATE TABLE dbo.B( IdOrdineProduzione int NOT NULL, IdDoc int NOT NULL, IdRiga int NOT NULL, CONSTRAINT PK_B PRIMARY KEY(IdOrdineProduzione, IdDoc, IdRiga), CONSTRAINT FK_BA FOREIGN KEY(IdOrdineProduzione) REFERENCES dbo.A(IdOrdineProduzione), CONSTRAINT FK_BC FOREIGN KEY(IdDoc, IdRiga) REFERENCES dbo.C(IdDoc, IdRiga) ); INSERT dbo.A(IdOrdineProduzione) VALUES (1); INSERT dbo.A(IdOrdineProduzione) VALUES (2); INSERT dbo.A(IdOrdineProduzione) VALUES (3); INSERT dbo.C VALUES (10, 1, '2011-01-01'); INSERT dbo.C VALUES (10, 2, '2011-02-02'); INSERT dbo.C VALUES (10, 3, '2011-03-03'); INSERT dbo.C VALUES (20, 1, '2011-04-04'); INSERT dbo.C VALUES (20, 2, NULL); INSERT dbo.C VALUES (30, 1, '2011-05-05'); INSERT dbo.B VALUES (1, 10, 1); INSERT dbo.B VALUES (1, 10, 2); INSERT dbo.B VALUES (1, 10, 3); INSERT dbo.B VALUES (2, 20, 1); INSERT dbo.B VALUES (2, 20, 2); INSERT dbo.B VALUES (3, 30, 1); UPDATE A SET DataConsegnaOrdine = (SELECT TOP 1 C.DataConsegna FROM dbo.B JOIN dbo.C ON B.IdDoc = C.IdDoc AND B.IdRiga = C.IdRiga WHERE B.IdOrdineProduzione = A.IdOrdineProduzione ORDER BY C.DataConsegna) FROM dbo.A SELECT * FROM dbo.A; /* Output: IdOrdineProduzione DataConsegnaOrdine ------------------ ----------------------- 1 2011-01-01 00:00:00.000 2 NULL 3 2011-05-05 00:00:00.000 (Righe interessate: 3) */ DROP TABLE dbo.B,dbo.C,dbo.A;
Ciao
Giorgio Rancati -
Grazie mille a entrambi per le risposte.
Purtroppo il cliente non mi aveva spiegato bene la situazione e c'è un accorgimento in più.
Solo nel caso in cui TUTTE le date siano NULL allora la data in TabellaA deve essere impostata con NULL. Se invece ci sono alcune date Null e altre impostate, allora devo prendere la minore di queste.
Usando la soluzione di Giorgio, a questo punto non ottengo quello che mi serve, in quanto se ci fosse anche solo una data NULL, verrebbe presa per prima e quindi usata per l'aggiornamento. Ma credo che non funzioni nemmeno quella di Lorenzo.
A proposito, la funzione COALESCE cosa fa in pratica? Se ho capito bene non sostituisce il valore NUll con quello indicato, ma lo considera come tale.
Se mi potete aiutare anche se vi ho fatto perdere tempo non avendovi dato da subito tutte le specifiche corrette, mi fareste un grosso favore.
Un'idea, anche se penso sia un po' grossolana, è quella di usare la funzione Min senza il COLAESCE (oppure SELECT TOP 1 ... ordinata per DataConsegna escludendo quelle a NULL) e poi in un secondo momento, aggiornare a NULL solo se TUTTE le date sono NULL. Ma come faccio a sapere se tutte le date legate a IdOrdineProduzione sono a Null?
Grazie
-
Ciao,
come hai già intuito, per il nuovo scenario basta escludere le date Null dalla subqueryUPDATE A SET DataConsegnaOrdine = (SELECT TOP 1 C.DataConsegna FROM dbo.B JOIN dbo.C ON B.IdDoc = C.IdDoc AND B.IdRiga = C.IdRiga WHERE B.IdOrdineProduzione = A.IdOrdineProduzione AND C.DataConsegna IS NOT NULL ORDER BY C.DataConsegna) FROM dbo.A
in questo modo se tutte le date di un IdOrdineProduzione
sono null la subquery non restituisce record e il campo viene aggiornato a NULL, altrimenti viene aggiornato con la data più bassa.
Per la funzione coalesce qui trovi tutte le info. COALESCE (Transact-SQL)
Ciao
Giorgio Rancati -
Solo nel caso in cui TUTTE le date siano NULL allora la data in TabellaA deve essere impostata con NULL. Se invece ci sono alcune date Null e altre impostate, allora devo prendere la minore di queste.
Con una piccola modifica otterrai il risultato desiderato:
USE tempdb; CREATE TABLE dbo.A( IdOrdineProduzione int NOT NULL, DataConsegnaOrdine datetime NULL, CONSTRAINT PK_A PRIMARY KEY(IdOrdineProduzione) ); CREATE TABLE dbo.C( IdDoc int NOT NULL, IdRiga int NOT NULL, DataConsegna datetime NULL, CONSTRAINT PK_C PRIMARY KEY(IdDoc, IdRiga) ); CREATE TABLE dbo.B( IdOrdineProduzione int NOT NULL, IdDoc int NOT NULL, IdRiga int NOT NULL, CONSTRAINT PK_B PRIMARY KEY(IdOrdineProduzione, IdDoc, IdRiga), CONSTRAINT FK_BA FOREIGN KEY(IdOrdineProduzione) REFERENCES dbo.A(IdOrdineProduzione), CONSTRAINT FK_BC FOREIGN KEY(IdDoc, IdRiga) REFERENCES dbo.C(IdDoc, IdRiga) ); INSERT dbo.A(IdOrdineProduzione) VALUES (1), (2), (3); INSERT dbo.C VALUES (10, 1, '2011-01-01'), (10, 2, '2011-02-02'), (10, 3, '2011-03-03') , (20, 1, '2011-04-04'), (20, 2, NULL) , (30, 1, NULL), (30, 2, NULL); INSERT dbo.B VALUES (1, 10, 1), (1, 10, 2), (1, 10, 3) , (2, 20, 1), (2, 20, 2) , (3, 30, 1), (3, 30, 2); UPDATE A SET DataConsegnaOrdine = Q.DataConsegna FROM dbo.A JOIN ( SELECT B.IdOrdineProduzione , MIN(C.DataConsegna) AS DataConsegna FROM dbo.B JOIN dbo.C ON B.IdDoc = C.IdDoc AND B.IdRiga = C.IdRiga WHERE C.DataConsegna IS NOT NULL GROUP BY B.IdOrdineProduzione ) AS Q ON A.IdOrdineProduzione = Q.IdOrdineProduzione; SELECT * FROM dbo.A; /* Output: IdOrdineProduzione DataConsegnaOrdine ------------------ ----------------------- 1 2011-01-01 00:00:00.000 2 2011-04-04 00:00:00.000 3 NULL (3 row(s) affected) */ DROP TABLE dbo.B, dbo.A, dbo.C;
Come già indicato da Giorgione è sufficiente escludere le righe con DataConsegna valorizzata a NULL.Ciao!
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo
http://social.technet.microsoft.com/Forums/it-IT/sqlserverit -
Di nuovo grazie mille a entrambi.
C'è ancora un problema:
La soluzione di Giorgio aggiorna a NULL la data della TabellaA nel caso in cui ci siano solo date NULL, ma lo fa anche se NON ci sono link con le altre tabelle. Invece in questo caso quella data non dovrebbe essere modificata
La soluzione di Lorenzo invece fa l'aggiornamento solo se trova legami, però se le date sono tutte a NULL, non aggiorna a NULL la data in TabellaA.
Nessuna idea per riuscire a ottenere entrambe le cose?
Come idea a me viene:
- uso la soluzione di Lorenzo
- poi faccio un'ulteriore aggiornamento x aggiornare a NULL la data in TabellaA solo se TUTTE le date sono NULL. Ma come faccio a sapere se tutte le date legate a IdOrdineProduzione sono a Null? Pensavo di verificare con una funzione di aggregazione se il numero di date per ogni IdOrdineProduzione coincide con il numero di date a NULL. E' fattibile?
Grazie
Patrizia
- Modificato patapatty giovedì 15 settembre 2011 07:47
-