Archive

Posts Tagged ‘SQL Server’

Transact-SQL: esempi di ROLLUP e GROUPING

March 1st, 2010 Nicola No comments

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.

Categories: Database, SQL Server Tags: , , , ,

C# e Sql Server: sp_help_job, monitorare i job.

January 29th, 2010 Nicola No comments

In questo link mamma microsoft ci parla della store procedure sp_hel_job, utile per monitorare i job di sql server:

http://technet.microsoft.com/it-it/library/ms186722(SQL.90).aspx

Il metodo di esempio DammiElencoJob riportato sotto non fa altro che eseguire la query:

"exec [msdb].dbo.sp_help_job @enabled = 1"

per avere l’elenco di tutti i job attivi.


private String DammiElencoJob()
{

StringBuilder _sb = new StringBuilder();
DataTable _ds;

_ds = QueryDB.getRecordSQL(, ,, , "exec [msdb].dbo.sp_help_job @enabled = 1");

if (_ds.Rows.Count == 0)

{
_sb.Append("Errore: nessun job trovato.").Append(Environment.NewLine);
}
else
{
_sb.Append("NOME                                                               + "
" |ULTIMA    |ESITO").Append(Environment.NewLine);
foreach (DataRow r in _ds.Rows)
{
string _name =  ((string)r["name"]).PadRight(90);
_sb.Append(_name).Append("|");
DateTime dt = new DateTime((int)r["last_run_date"]);
string _last_run_date = ("" + dt.ToString("dd/MM/yyyy")).PadRight(10);
_sb.Append(_last_run_date).Append("|");
int _last_run_outcome = (int)r["last_run_outcome"];
switch (_last_run_outcome)
{
case 0:
_sb.Append("Non completato");
break;
case 1:
_sb.Append("Completato");
break;
case 3:
_sb.Append("Annullato");
break;
case 5:
_sb.Append("Stato sconosciuto");
break;
default:
_sb.Append("Stato sconosciuto");
break;
}
_sb.Append(Environment.NewLine);
}
}
_sb.Append(Environment.NewLine);
return _sb.ToString();

}


public static DataTable getRecordSQL(String datasource,String user, String password, String database, String sql)
{

String connStr;
OleDbConnection conn;
OleDbDataAdapter adattatore;
DataTable ds = new DataTable();

connStr = @"Provider = SQLOLEDB.1;Password=" + password + ";User ID=" + user + ";Initial Catalog=" + database + ";Data Source=" + datasource;

conn = new OleDbConnection(connStr);
adattatore = new OleDbDataAdapter();
adattatore.SelectCommand = new OleDbCommand(sql, conn);

try

{
adattatore.Fill(ds);
adattatore.Dispose();
conn.Dispose();
return ds;
}
catch (SqlException se)
{
return null;
}
catch (Exception ex)
{
return null;
}

}

Categories: C#, SQL Server Tags: , , ,

Transact-SQL: errore 8153 – è stato eliminato un valore Null …

January 25th, 2010 Nicola No comments

Ebbene si, ogni tanto si torna da mamma microsoft. Oggi una store procedure mi ha dato il seguente errore:

Avviso: è stato eliminato un valore Null tramite un'aggregazione o un'altra operazione SET.

Ho trovato il codice dell’errore qua: http://msdn.microsoft.com/it-it/library/cc645597.aspx ed in questo forum anche la spiegazione:

http://www.btre.it/forum/select_sum-t53361.html

In sostanza, significa che in una select contenente una formula di aggregazione (es SUM) è stato scartato un record che nel campo su cui si effettua l’aggregazione ha valore NULL.

Categories: SQL Server Tags: ,