Il blog di Nicola

Mi sono imbattuto in questa problematica: utilizzare una SELECT TOP all’interno di un ciclo, in cui il numero di record da utilizzare è assegnato ad una variable.
Questo è l’esempio che non funziona:

DECLARE @num INT
 
DECLARE c_curs CURSOR FOR
   [...]
OPEN c_curs
 
FETCH NEXT FROM c_curs
INTO @num
 
WHILE @@FETCH_STATUS = 0
BEGIN
   [...]
   SELECT TOP @num [...] ORDER BY NEWID()
 
   FETCH NEXT FROM c_curs
   INTO @anno, @num
END
 
CLOSE c_curs
DEALLOCATE c_curs

Ho risolto utilizzando ROWCOUNT che è invece parametrico:

SET ROWCOUNT @num
SELECT [...] ORDER BY NEWID()
SET ROWCOUNT 0
 
FETCH NEXT FROM c_curs
INTO @num

E’ importante ricordarsi di ripristinare il valore di ROWCOUNT a 0.

Curiosando in rete ho trovato questo articolo che spiega meglio il problema, e offre più di una soluzione: http://sqlserver2000.databases.aspfaq.com/how-do-i-use-a-variable-in-a-top-clause-in-sql-server.html

Si può fare utilizzando SELECT TOP 1 … ORDER BY NEWID(), da una tabella contenente i caratteri disponibili. Ecco l’esempio:

WITH char_table AS (
	SELECT 'a' AS a
    UNION
	SELECT 'b' AS a
        ...
        /* inserire qui l'elenco dei caratteri che si 
            vogliono prendere in considerazione */
        ...
	UNION
	SELECT 'z' AS a
)
SELECT
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid())

Date due tabelle TABELLA_SORGENTE e TABELLA_DESTINAZIONE, per copiare tabella sorgente su tabella destinazione ci sono anche queste due soluzioni:

• Utilizzo dell’accoppiata DROP TABLE + SELECT INTO: assolutamente da EVITARE se avete a cuore la vostra tabella di destinazione.
• Utilizzo dell’accoppiata DELETE + INSERT: sicuramente il metodo da usare.

Il motivi per non usare l’accoppiata DROP TABLE + SELECT INTO sta nel fatto che tutti i CONSTRAINT ed i vincoli strutturale presenti sulla tabella che viene eliminata non vengono ovviamente ricreati dalla SELECT INTO ma vengono persi. Quella che viene creata è una tabella ad-hoc per i dati che deve contenere, nulla di più.

Un esempio concreto:

Creiamo una tabella sorgente e mettiamoci dei dati dentro:

CREATE TABLE TABELLA_SORGENTE (ID INT, DESCRIZIONE VARCHAR(20) NOT NULL DEFAULT 'PIPPO')

INSERT INTO TABELLA_SORGENTE VALUES (1,'PAPERINO');
INSERT INTO TABELLA_SORGENTE VALUES (2,'PLUTO');


Creiamo la tabella di destinazione, speculare alla tabella sorgente:

CREATE TABLE TABELLA_DESTINAZIONE (ID INT, DESCRIZIONE VARCHAR(20) NOT NULL DEFAULT 'PIPPO')

Utilizzando DELETE + INSERT abbiamo:

DELETE FROM TABELLA_DESTINAZIONE
INSERT INTO TABELLA_DESTINAZIONE SELECT * FROM TABELLA_SORGENTE

A questo punto la query

INSERT INTO TABELLA_DESTINAZIONE (ID) VALUES (3)

va a buon fine, e la tabella si presenta con questi record

1 PAPERINO
2 PLUTO
3 PIPPO


Utilizzando invece DROP TABLE + SELECT INTO abbiamo:

DROP TABLE TABELLA_DESTINAZIONE
SELECT * INTO TABELLA_DESTINAZIONE FROM TABELLA_SORGENTE

Sembrerebbe tutto uguale, ma il risultato della query

INSERT INTO TABELLA_DESTINAZIONE (ID) VALUES (3)

è il seguente:

Cannot insert the value NULL into column 'DESCRIZIONE', table 'NICOLA.dbo.TABELLA_DESTINAZIONE'; column does not allow nulls. INSERT fails.
The statement has been terminated.


Questo perchè la condizione DEFAULT ‘PIPPO’ della dichiarazione iniziale della tabella

CREATE TABLE TABELLA_DESTINAZIONE (ID INT, DESCRIZIONE VARCHAR(20) NOT NULL DEFAULT 'PIPPO')

ora non è più presente.
Quindi non fate mai DROP TABLE + SELECT INTO su tabelle cui volete bene.

Ecco degli esempi della funzione di aggregazione GROUPING associata all’utilizzo del GROUP BY WITH ROLLUP

GROUPING è una funzione di aggregazione che aggiunge una colonna al resultset, valorizzata ad 1 per ogni record aggiunto dalla clausola  CUBE o ROLLUP.

Questi esempi si applicano a SQL Server 2005. Le clausole ROLLUP e CUBE, e la funzione GROUPING sono presenti anche in altri DBMS.

Creiamo una tabella ORDINI con questi campi:

CREATE TABLE [ORDINI] (
[ID_ORDINE] [int] NOT NULL ,
[PRODOTTO] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[QTA] [int] NULL ,
[PREZZO] [money] NULL ,
CONSTRAINT [PK_ORDINI] PRIMARY KEY CLUSTERED
(
[ID_ORDINE],
[PRODOTTO]
) ON [PRIMARY]
) ON [PRIMARY]
GO


con i seguenti dati (con prezzi casuali):

INSERT INTO ORDINI VALUES (1, 'PS3', 1, 249.99)
INSERT INTO ORDINI VALUES (2, 'PS3', 1, 249.99)
INSERT INTO ORDINI VALUES (2, 'XBOX', 2, 499.99)
INSERT INTO ORDINI VALUES (3, 'WII', 1, 149.99)
INSERT INTO ORDINI VALUES (3, 'PS3', 1, 249.99)
INSERT INTO ORDINI VALUES (3, 'XBOX', 1, 199.99)

Quindi, tanto per dire, l’ordine 1 composto da una PS3, l’ordine 2 composto da una PS3 e due XBOX e l’ordine 3 con una WII, una PS3 e una XBOX.
Il risultato è questo:

SELECT * FROM ORDINI

ID_ORDINE	PRODOTTO	QTA	PREZZO
1	PS3	1	249.9900
2	PS3	1	249.9900
2	XBOX	2	499.9900
3	PS3	1	249.9900
3	WII	1	149.9900
3	XBOX	1	199.9900

Se eseguiamo un normale GROUP BY sul prodotto otteniamo:

SELECT PRODOTTO, SUM(QTA) AS QUANTITA, SUM(PREZZO) AS PREZZO
FROM ORDINI
GROUP BY PRODOTTO

PRODOTTO	QTA	PREZZO
PS3	3	749.9700
WII	1	149.9900
XBOX	3	699.9800

Aggiungendo la clausola WITH ROLLUP sul prodotto otteniamo la riga aggiuntiva con la somma del GROUP BY relativo (evidenziata in grassetto):

SELECT PRODOTTO, SUM(QTA) AS QUANTITA, SUM(PREZZO) AS PREZZO
FROM ORDINI
GROUP BY PRODOTTO WITH ROLLUP

PRODOTTO	QTA	PREZZO
PS3	3	749.9700
WII	1	149.9900
XBOX	3	699.9800
NULL	7	1599.9400

Adesso vediamo l’effetto della funzione GROUPING sul campo PRODOTTO, e vediamo che la colonna ‘GROUPING PRODOTTO è valorizzata ad 1 solo per il record aggiunto dal WITH ROLLUP:

SELECT PRODOTTO, SUM(QTA) AS QUANTITA, SUM(PREZZO) AS PREZZO, GROUPING(PRODOTTO) AS 'GROUPING PRODOTTO'
FROM ORDINI
GROUP BY PRODOTTO WITH ROLLUP

PRODOTTO	QTA	PREZZO	GROUPING PRODOTTO
PS3	3	749.9700	0
WII	1	149.9900	0
XBOX	3	699.9800	0
NULL	7	1599.9400	1

Adesso raggruppiamo oltre che per prodotto anche per id dell’ordine:

SELECT PRODOTTO, ID_ORDINE, SUM(QTA) AS QUANTITA, SUM(PREZZO) AS PREZZO
FROM ORDINI
GROUP BY PRODOTTO, ID_ORDINE

PRODOTTO	ID_ORDINE	QTA	PREZZO
PS3	1	1	249.9900
PS3	2	1	249.9900
XBOX	2	2	499.9900
PS3	3	1	249.9900
WII	3	1	149.9900
XBOX	3	1	149.9900

E aggiungiamo il ROLLUP. Ogni raggruppamento genera una riga di totale:

SELECT PRODOTTO, ID_ORDINE, SUM(QTA) AS QUANTITA, SUM(PREZZO) AS PREZZO
FROM ORDINI
GROUP BY PRODOTTO, ID_ORDINE WITH ROLLUP

PRODOTTO	ID_ORDINE	QTA	PREZZO
PS3	1	1	249.9900
PS3	2	1	249.9900
PS3	3	1	249.9900
PS3	NULL	3	749.9700
WII	3	1	149.9900
WII	NULL	1	149.9900
XBOX	2	2	499.9900
XBOX	3	1	199.9900
XBOX	NULL	3	699.9800
NULL	NULL	7	1599.9400

E adesso aggiungiamo due GROUPING, uno per PRODOTTO e uno per ID_ORDINE. Ecco il risultato.

SELECT PRODOTTO, ID_ORDINE, SUM(QTA) AS QUANTITA, SUM(PREZZO) AS PREZZO, GROUPING(ID_ORDINE) AS 'GROUPING ORDINE', GROUPING(PRODOTTO) AS 'GROUPING PRODOTTO'
FROM ORDINI
GROUP BY PRODOTTO, ID_ORDINE WITH ROLLUP

PRODOTTO	ID_ORDINE	QTA	PREZZO	GROUPING ORDINE	GROUPING PRODOTTO
PS3	1	1	249.9900	0	0
PS3	2	1	249.9900	0	0
PS3	3	1	249.9900	0	0
PS3	NULL	3	749.9700	1	0
WII	3	1	149.9900	0	0
WII	NULL	1	149.9900	1	0
XBOX	2	2	499.9900	0	0
XBOX	3	1	199.9900	0	0
XBOX	NULL	3	699.9800	1	0
NULL	NULL	7	1599.9400	1	1

Nella stessa query abbiamo, per ogni prodotto, una riga col dettaglio per ogni ordine, ed una riga di totale, più una riga di totale generale.

Transact-SQL: L’operatore ROLLUP

L’operatore ROLLUP aggiunge ai record generati dal GROUP BY i totali relativi ai raggruppamenti, ed il totale generale dei valori calcolati. Non costruisce l’intero cubo su tutte le combinazioni dei campi inseriti nel GROUP BY, ma segue l’ordine del GROUP BY in questo modo:

SELECT , , SUM(
FROM
GROUP BY ,, WITH ROLLUP


Come per il CUBE questa versione viene definita Non-ISO-Compliant dalla Microsoft e da SQL Server 2008 la sua sintassi è:

SELECT , , SUM(
FROM
GROUP BY ROLLUP(,,)


Riprendendo lo stesso esempio fatto il CUBE:

CREATE TABLE VENDITE (AGENTE VARCHAR(20), PRODOTTO VARCHAR(20), PAESE VARCHAR(20), QUANTITA INT)

INSERT INTO VENDITE VALUES ('AGENTE 1', 'PS3', 'ITALIA', 20)
INSERT INTO VENDITE VALUES ('AGENTE 1', 'PS3', 'SPAGNA', 30)
INSERT INTO VENDITE VALUES ('AGENTE 1', 'PS3', 'FRANCIA', 10)
INSERT INTO VENDITE VALUES ('AGENTE 1', 'PS3', 'GERMANIA', 15)
INSERT INTO VENDITE VALUES ('AGENTE 1', 'WII', 'ITALIA', 10)
INSERT INTO VENDITE VALUES ('AGENTE 1', 'WII', 'SPAGNA', 25)
INSERT INTO VENDITE VALUES ('AGENTE 1', 'WII', 'INGHILTERRA', 8)
INSERT INTO VENDITE VALUES ('AGENTE 1', 'WII', 'GERMANIA', 35)

INSERT INTO VENDITE VALUES ('AGENTE 2', 'PS3', 'ITALIA', 22)
INSERT INTO VENDITE VALUES ('AGENTE 2', 'PS3', 'SPAGNA', 32)
INSERT INTO VENDITE VALUES ('AGENTE 2', 'PS3', 'FRANCIA', 16)
INSERT INTO VENDITE VALUES ('AGENTE 2', 'PS3', 'GERMANIA', 19)
INSERT INTO VENDITE VALUES ('AGENTE 2', 'WII', 'ITALIA', 23)
INSERT INTO VENDITE VALUES ('AGENTE 2', 'WII', 'SPAGNA', 14)
INSERT INTO VENDITE VALUES ('AGENTE 2', 'WII', 'INGHILTERRA', 7)
INSERT INTO VENDITE VALUES ('AGENTE 2', 'WII', 'GERMANIA', 33)


dove ogni Agente figura con la quantità venduta per prodotto e nazione.

La query

SELECT AGENTE, PRODOTTO, PAESE, SUM(QUANTITA)
FROM VENDITE
GROUP BY AGENTE, PRODOTTO, PAESE WITH ROLLUP


restituisce un record con la somma del campo quantità per ogni raggruppamento in ordine di GROUP BY, quindi (AGENTE) e (AGENTE/PRODOTTO), visto che (AGENTE/PRODOTTO/PAESE) è una totalizzazione che viene già generata dal GROUP BY. Ecco il risultato (in grassetto i campi aggiunto da WITH ROLLUP):

AGENTE 1 PS3 FRANCIA     10
AGENTE 1 PS3 GERMANIA    15
AGENTE 1 PS3 ITALIA      20
AGENTE 1 PS3 SPAGNA      30
AGENTE 1 PS3 NULL        75
AGENTE 1 WII GERMANIA    35
AGENTE 1 WII INGHILTERRA 8
AGENTE 1 WII ITALIA      10
AGENTE 1 WII SPAGNA      25
AGENTE 1 WII NULL        78
AGENTE 1 NULL NULL       153
AGENTE 2 PS3 FRANCIA     16
AGENTE 2 PS3 GERMANIA    19
AGENTE 2 PS3 ITALIA      22
AGENTE 2 PS3 SPAGNA      32
AGENTE 2 PS3 NULL        89
AGENTE 2 WII GERMANIA    33
AGENTE 2 WII INGHILTERRA 7
AGENTE 2 WII ITALIA      23
AGENTE 2 WII SPAGNA      14
AGENTE 2 WII NULL        77
AGENTE 2 NULL NULL       166
NULL NULL NULL           319


Quindi, per ogni AGENTE:

AGENTE 1 NULL NULL 153
AGENTE 2 NULL NULL 166


Per ogni raggruppamento AGENTE/PRODOTTO:

AGENTE 1 PS3 NULL 75
AGENTE 1 WII NULL 78
AGENTE 2 PS3 NULL 89
AGENTE 2 WII NULL 77


E infine è presente anche il totale dei totali:

NULL NULL NULL 319