Alert on data deletion

We have an employee table that contains bank details and are experiencing
problems with account numbers being erased and lost. In order to track down
why this is happening (either due to our application code or SQL
replication) we'd like to be able to prevent certain columns from being
deleted if they already contain some data.

Is it possible to setup a check constraint to prevent our ee_acct_no columns
from being set to NULL or blank strings if it contains an account number
(i.e a 9 digit number)? We have setup the column to allow NULL's as we don't
always know employees bank details until later, so we do need to put them on
our database without bank details initially.

Also, if possible, can someone suggest a stored procedure or trigger i could
create that would fire a user-defined error message that would email an
operator if a bank account number changed?

Many thanks

Dan Williams.On Thu, 10 Mar 2005 14:35:55 +0000 (UTC), Dan Williams wrote:

> We have an employee table that contains bank details and are experiencing
> problems with account numbers being erased and lost. In order to track down
> why this is happening (either due to our application code or SQL
> replication) we'd like to be able to prevent certain columns from being
> deleted if they already contain some data.
> Is it possible to setup a check constraint to prevent our ee_acct_no columns
> from being set to NULL or blank strings if it contains an account number
> (i.e a 9 digit number)? We have setup the column to allow NULL's as we don't
> always know employees bank details until later, so we do need to put them on
> our database without bank details initially.

If ee_acct_no starts out null and later becomes non-null, then a check
constraint can't do the trick. You need a trigger ... which answers the
next question

> Also, if possible, can someone suggest a stored procedure or trigger i could
> create that would fire a user-defined error message that would email an
> operator if a bank account number changed?

CREATE TRIGGER trig_ee_acct
ON ee_acct
FOR UPDATE
AS
IF UPDATE(ee_acct_no)
BEGIN
declare @.msg varchar(400)
RAISERROR ('The ee_acct_no column must never be changed', 16, 1)
ROLLBACK TRANSACTION
END
GO

As Books Online describes:

All ad hoc messages have a standard message ID of 14,000.

Therefore in enterprise manager, you can set an email alert on message ID
14,000, and operators will get an email.

> Many thanks
> Dan Williams.|||Ross Presser (rpresser@.imtek.com) writes:
> As Books Online describes:
> All ad hoc messages have a standard message ID of 14,000.
> Therefore in enterprise manager, you can set an email alert on message ID
> 14,000, and operators will get an email.

Is that SQL 7 Books Online? The number for ad hoc messages is 50000.

Dan could also use sp_addmessage to add a custom message, for instance
75321 for this error, and say:

RAISERROR(75321, 16, 1)

and the set up the alert on this code.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Dan Williams (dtwilliams@.hotmail.com) writes:
> We have an employee table that contains bank details and are
> experiencing problems with account numbers being erased and lost. In
> order to track down why this is happening (either due to our application
> code or SQL replication) we'd like to be able to prevent certain columns
> from being deleted if they already contain some data.
> Is it possible to setup a check constraint to prevent our ee_acct_no
> columns from being set to NULL or blank strings if it contains an
> account number (i.e a 9 digit number)? We have setup the column to allow
> NULL's as we don't always know employees bank details until later, so we
> do need to put them on our database without bank details initially.

That would have to be a trigger. Ross showed you the basics, but I like
to add some more details.

First IF UPDATE() a bit heavy-handed. IF UPDATE() only tells us that
the column mentioned in the SET clause, but not that the value was
actually changed.

So, you would have to compare inserted and deleted with each other
to compare these. However, joining inserted and deleted can have
costly performance effects. My standard routine is to save inserted
and deleted into table variables and then work with these.

Note that it's a good idea to keep IF UPDATE(), so that you don't
perform the check if the UPDATE is for a completely different column
only.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Thu, 10 Mar 2005 21:46:25 +0000 (UTC), Erland Sommarskog wrote:

> Ross Presser (rpresser@.imtek.com) writes:
>> As Books Online describes:
>>
>> All ad hoc messages have a standard message ID of 14,000.
>>
>> Therefore in enterprise manager, you can set an email alert on message ID
>> 14,000, and operators will get an email.
> Is that SQL 7 Books Online? The number for ad hoc messages is 50000.
> Dan could also use sp_addmessage to add a custom message, for instance
> 75321 for this error, and say:
> RAISERROR(75321, 16, 1)
> and the set up the alert on this code.

SQL 2000 BOL. And it's self-contradictory!

msg_id

Is a user-defined error message stored in the sysmessages table. Error
numbers for user-defined error messages should be greater than 50,000. Ad
hoc messages raise an error of 50,000.

msg_str

Is an ad hoc message with formatting similar to the PRINTF format style
used in C. The error message can have up to 400 characters. If the message
contains more than 400 characters, only the first 397 will be displayed and
an ellipsis will be added to indicate that the message has been cut. All ad
hoc messages have a standard message ID of 14,000.|||That's great. Thanks a lot.

I've managed to create a trigger that makes use of custom made error
messages that get emailed to me whenever an account number gets
changed. Here is my SQL code:-

CREATE TRIGGER trig_ee_acct ON employee
FOR UPDATE
AS

DECLARE @.eecode varchar(30)
SET @.eecode = (select ee_code FROM inserted)

IF UPDATE(ee_acct_no)
DECLARE @.oldAcctNo varchar(10)
DECLARE @.newAcctNo varchar(10)

SET @.oldAcctNo = (select ee_acct_no from deleted)
SET @.newAcctNo = (select ee_acct_no from inserted)

IF LEN(@.oldAcctNo) > 0 AND @.newAcctNo = ''
BEGIN
RAISERROR ('Bank Account Numbers cannot be deleted.', 16, 1)
ROLLBACK TRANSACTION
END

IF LEN(@.newAcctNo) > 0 AND LEN(@.newAcctNo) < 8
BEGIN
RAISERROR ('Bank Account Numbers must be 8 digits long.', 16,
1)
ROLLBACK TRANSACTION
END

IF LEN(@.newAcctNo) = 8 AND @.newAcctNo <> @.oldAcctNo
BEGIN
RAISERROR (50001, 10, 1, @.eecode, @.oldAcctNo, @.newAcctNo)
END

I believe my logic could probably be made more efficient, but initially
this appears to be working. However, i am experiencing issues with my
custom alert not being triggered consistently when the account numbers
are being changed. Do the insert and delete trigger tables get purged
after the trigger fires?

After updating an employees account number, i do initially receive the
email alert (with details of the old and new numbers), but if i update
it immediately after, it doesn't seem to fire the alert. However, if i
wait a couple of minutes, it appears to work ok.

That's why i'm thinking the inserted and deleted tables still contain
the previously saved information. Is there anyway i can purge the
information?

Thanks again

Dan|||dtwilliams@.hotmail.com (dan_williams@.newcross-nursing.com) writes:
> CREATE TRIGGER trig_ee_acct ON employee
> FOR UPDATE
> AS
> DECLARE @.eecode varchar(30)
> SET @.eecode = (select ee_code FROM inserted)
> IF UPDATE(ee_acct_no)
> DECLARE @.oldAcctNo varchar(10)
> DECLARE @.newAcctNo varchar(10)
> SET @.oldAcctNo = (select ee_acct_no from deleted)
> SET @.newAcctNo = (select ee_acct_no from inserted)

This is not a good trigger. A trigger fires once per statement, and
the deleted/inserted tables can contain many rows, and a good trigger
should handle this. So you will need to rewrite your trigger.

> That's why i'm thinking the inserted and deleted tables still contain
> the previously saved information. Is there anyway i can purge the
> information?

deleted/inserted are so-called virtual tables and are constructed from
the transaction log, and they cannot be accessed outside the scope of
the trigger.

By the way, if you need to make many accesses to the tables in your
trigger, it's a good idea to copy the interesting columns to table
variables and work with these instead. This can give quite some
performance improvements.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9616EB37A93C5Yazorman@.127.0.0.1...
> dtwilliams@.hotmail.com (dan_williams@.newcross-nursing.com) writes:
>> CREATE TRIGGER trig_ee_acct ON employee
>> FOR UPDATE
>> AS
>>
>> DECLARE @.eecode varchar(30)
>> SET @.eecode = (select ee_code FROM inserted)
>>
>> IF UPDATE(ee_acct_no)
>> DECLARE @.oldAcctNo varchar(10)
>> DECLARE @.newAcctNo varchar(10)
>>
>> SET @.oldAcctNo = (select ee_acct_no from deleted)
>> SET @.newAcctNo = (select ee_acct_no from inserted)
> This is not a good trigger. A trigger fires once per statement, and
> the deleted/inserted tables can contain many rows, and a good trigger
> should handle this. So you will need to rewrite your trigger.
>> That's why i'm thinking the inserted and deleted tables still contain
>> the previously saved information. Is there anyway i can purge the
>> information?
> deleted/inserted are so-called virtual tables and are constructed from
> the transaction log, and they cannot be accessed outside the scope of
> the trigger.
> By the way, if you need to make many accesses to the tables in your
> trigger, it's a good idea to copy the interesting columns to table
> variables and work with these instead. This can give quite some
> performance improvements.

OK, thanks for the advice. I've just discovered the wonders of triggers and
have previously been performing validation and integrity checks in my
application code, so i'm very much a beginner in writing SQL code.

Could you provide me with an example trigger that i can use or point me in
the direction of a good web site that i can learn from?

Many thanks

Dan|||Dan Williams (dtwilliams@.hotmail.com) writes:
> OK, thanks for the advice. I've just discovered the wonders of triggers
> and have previously been performing validation and integrity checks in
> my application code, so i'm very much a beginner in writing SQL code.
> Could you provide me with an example trigger that i can use or point me in
> the direction of a good web site that i can learn from?

Writing triggers is not fundamentaly different from writing stored
procedures, although a few things apply:

1) The "inserted" and "deleted" are visible in the trigger only, not
from stored procedures or dynamic SQL called from the trigger.
2) The tables are slow to access, so if the trigger has many references
to them, copying to a table variable is recommendable.
3) You are always in the context of the transaction defined by the statement
that fired the trigger. For this reason, one should engage in long-
running operations, as this can give contention problems.
4) Any error (save RAISERROR) terminates execution, aborts the batch and
rolls back the transaction.
5) Likewise, if the transaction count on exit differs from the trancount
when the trigger started execution, this also causes the entire batch
to be rolled back.

The trigger you posted could be rewritten to something like:

CREATE TRIGGER trig_ee_acct ON employee
FOR UPDATE
AS

DECLARE @.inserted TABLE (...)
DECLARE @.deleted TABLE (...)

INSERT @.inserted (...)
SELECT ... FROM inserted

INSERT @.deleted (...9
SELECT ... FROM deleted

IF UPDATE(ee_acct_no)
BEGIN
IF EXISTS (SELECT *
FROM @.inserted i
JOIN @.deleted d ON i.pk = d.pk
WHERE len(d.oldAcctNo) > 0
AND nullif(i.newAcctNo, '') IS NULL
BEGIN
RAISERROR ('Bank Account Numbers cannot be deleted.', 16, 1)
ROLLBACK TRANSACTION
END

IF EXISTS (SELECT *
FROM @.inserted i
WHERE len(i.oldAcctNo) <> 8)
BEGIN
RAISERROR ('Bank Account Numbers must be 8 digits long.', 16, 1)
ROLLBACK TRANSACTION
END

IF EXISTS (SELECT *
FROM @.inserted i
JOIN @.deleted d ON i.pk = d.pk
WHERE d.oldAcctNo <> i.newAcctNo
BEGIN
RAISERROR (50001, 10, 1, @.eecode, @.oldAcctNo, @.newAcctNo)
ROLLBACK TRANSACTION
END
END

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

aggregation question

Aggregation SUM adds the numbers together. Is there a similar aggregation function that returns the product of all the numbers?

Thanks

No, there is no built-in PRODUCT aggregate. One thing you could try is taking the exponent of the sum of the logs of the individual rows; however, this will tend to cause arithmetic execution errors if any of the individual rows have zero for a value.

Here is a similar discussion:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=355075&SiteID=1

|||

Do you mean 'product' as in factorial?

|||

Check out the link below for a solution that uses the built-in aggregates to compute product:

http://www.umachandar.com/technical/SQL6x70Scripts/Main21.htm

You can also write a SQLCLR aggregate in SQL Server 2005 to do the same.

aggregates in stored procedure

I have three tables, [Versions], [RCF Numbers] and [Call Counts]. [Versions]
is in a one to many relationship with [RCF Numbers], and [RCF Numbers] is on
e
to many with [Call Counts].
I need to update [Versions].[Number Months Reported] with the highest value
in [Call Counts].[Month Number] (technically, the highest value – 3), but
when I write a stored procedure to do this I get an error saying the
procedure is not updateable because the underlying query contains aggregates
.
Is there a way around this?
Here's the relevant table structure:
################################
CREATE TABLE [Versions] (
[Version ID] [int] IDENTITY (1, 1) NOT NULL ,
[Test ID] [int] NULL CONSTRAINT [DF__Versions__Test I__6E8B6712] DEFAULT (0),
[Version Type] [smallint] NULL ,
[Number Months Reported] [int] NULL CONSTRAINT [DF_Versions_Number Months
Reported] DEFAULT (0),
(
[Version ID]
) ON [PRIMARY] ,
CONSTRAINT [Versions_FK00] FOREIGN KEY
(
[Test ID]
) REFERENCES [Tests] (
[Test ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
################################
CREATE TABLE [RCF Numbers] (
[RCF Number ID] [int] IDENTITY (1, 1) NOT NULL ,
[Version ID] [int] NULL ,
[RCF Number] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Termination Number] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
CONSTRAINT [PK_RCF Numbers] PRIMARY KEY CLUSTERED
(
[RCF Number ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_RCF Numbers_Versions] FOREIGN KEY
(
[Version ID]
) REFERENCES [Versions] (
[Version ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
################################
CREATE TABLE [Call Counts] (
[Call Count ID] [int] IDENTITY (1, 1) NOT NULL ,
[RCF Number ID] [int] NULL ,
[Month Number] [smallint] NULL ,
CONSTRAINT [PK_Call Counts] PRIMARY KEY CLUSTERED
(
[Call Count ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Call Counts_RCF Numbers] FOREIGN KEY
(
[RCF Number ID]
) REFERENCES [RCF Numbers] (
[RCF Number ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
################################
Here's a view to query the correct value:
CREATE VIEW dbo.vwCalcMonthsReported
AS
SELECT MAX(dbo.[Call Counts].[Month Number]) AS [Months of Data],
dbo.Versions.[Number Months Reported], dbo.[RCF Numbers].[Version ID]
FROM dbo.Versions INNER JOIN
dbo.[RCF Numbers] ON dbo.Versions.[Version ID] =
dbo.[RCF Numbers].[Version ID] INNER JOIN
dbo.[Call Counts] ON dbo.[RCF Numbers].[RCF Number ID]
= dbo.[Call Counts].[RCF Number ID]
GROUP BY dbo.Versions.[Number Months Reported], dbo.[RCF Numbers].[Version I
D]
HAVING (MAX(dbo.[Call Counts].[Month Number]) > 3)
GO
################################
Here's my stored procedure:
CREATE PROCEDURE dbo.sp_Update_Months_Reported
AS UPDATE dbo.vwCalcMonthsReported
SET [Number Months Reported] = [Months of Data] - 3
GO
#####################Hi Mike,
You could consider an Instead Of trigger on the view for your updates.
http://msdn.microsoft.com/library/d...>
nsteadof.asp
Cheers,
Steve Goodyear
Vancouver, BC
"mike" wrote:

> I have three tables, [Versions], [RCF Numbers] and [Call Counts]. [Versions]
> is in a one to many relationship with [RCF Numbers], and [RCF Numbers] is
one
> to many with [Call Counts].
> I need to update [Versions].[Number Months Reported] with the highest value
> in [Call Counts].[Month Number] (technically, the highest value – 3), bu
t
> when I write a stored procedure to do this I get an error saying the
> procedure is not updateable because the underlying query contains aggregat
es.
> Is there a way around this?
> Here's the relevant table structure:
> ################################
> CREATE TABLE [Versions] (
> [Version ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Test ID] [int] NULL CONSTRAINT [DF__Versions__Test I__6E8B6712] DEFAULT (0),
> [Version Type] [smallint] NULL ,
> [Number Months Reported] [int] NULL CONSTRAINT [DF_Versions_Number Months
> Reported] DEFAULT (0),
> (
> [Version ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [Versions_FK00] FOREIGN KEY
> (
> [Test ID]
> ) REFERENCES [Tests] (
> [Test ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> ################################
> CREATE TABLE [RCF Numbers] (
> [RCF Number ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Version ID] [int] NULL ,
> [RCF Number] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Termination Number] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> CONSTRAINT [PK_RCF Numbers] PRIMARY KEY CLUSTERED
> (
> [RCF Number ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_RCF Numbers_Versions] FOREIGN KEY
> (
> [Version ID]
> ) REFERENCES [Versions] (
> [Version ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
> GO
> ################################
> CREATE TABLE [Call Counts] (
> [Call Count ID] [int] IDENTITY (1, 1) NOT NULL ,
> [RCF Number ID] [int] NULL ,
> [Month Number] [smallint] NULL ,
> CONSTRAINT [PK_Call Counts] PRIMARY KEY CLUSTERED
> (
> [Call Count ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Call Counts_RCF Numbers] FOREIGN KEY
> (
> [RCF Number ID]
> ) REFERENCES [RCF Numbers] (
> [RCF Number ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
> GO
> ################################
> Here's a view to query the correct value:
> CREATE VIEW dbo.vwCalcMonthsReported
> AS
> SELECT MAX(dbo.[Call Counts].[Month Number]) AS [Months of Data],
> dbo.Versions.[Number Months Reported], dbo.[RCF Numbers].[Version ID]
> FROM dbo.Versions INNER JOIN
> dbo.[RCF Numbers] ON dbo.Versions.[Version ID] =
> dbo.[RCF Numbers].[Version ID] INNER JOIN
> dbo.[Call Counts] ON dbo.[RCF Numbers].[RCF Number I
D]
> = dbo.[Call Counts].[RCF Number ID]
> GROUP BY dbo.Versions.[Number Months Reported], dbo.[RCF Numbers].[Version
ID]
> HAVING (MAX(dbo.[Call Counts].[Month Number]) > 3)
> GO
> ################################
> Here's my stored procedure:
> CREATE PROCEDURE dbo.sp_Update_Months_Reported
> AS UPDATE dbo.vwCalcMonthsReported
> SET [Number Months Reported] = [Months of Data] - 3
> GO
> #####################
>|||Your view has aggregate funtions in select list. So you can not update it
with stored procedure. You can create INSTEAD OF triggers.
"mike" wrote:

> I have three tables, [Versions], [RCF Numbers] and [Call Counts]. [Versions]
> is in a one to many relationship with [RCF Numbers], and [RCF Numbers] is
one
> to many with [Call Counts].
> I need to update [Versions].[Number Months Reported] with the highest value
> in [Call Counts].[Month Number] (technically, the highest value – 3), bu
t
> when I write a stored procedure to do this I get an error saying the
> procedure is not updateable because the underlying query contains aggregat
es.
> Is there a way around this?
> Here's the relevant table structure:
> ################################
> CREATE TABLE [Versions] (
> [Version ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Test ID] [int] NULL CONSTRAINT [DF__Versions__Test I__6E8B6712] DEFAULT (0),
> [Version Type] [smallint] NULL ,
> [Number Months Reported] [int] NULL CONSTRAINT [DF_Versions_Number Months
> Reported] DEFAULT (0),
> (
> [Version ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [Versions_FK00] FOREIGN KEY
> (
> [Test ID]
> ) REFERENCES [Tests] (
> [Test ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> ################################
> CREATE TABLE [RCF Numbers] (
> [RCF Number ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Version ID] [int] NULL ,
> [RCF Number] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Termination Number] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> CONSTRAINT [PK_RCF Numbers] PRIMARY KEY CLUSTERED
> (
> [RCF Number ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_RCF Numbers_Versions] FOREIGN KEY
> (
> [Version ID]
> ) REFERENCES [Versions] (
> [Version ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
> GO
> ################################
> CREATE TABLE [Call Counts] (
> [Call Count ID] [int] IDENTITY (1, 1) NOT NULL ,
> [RCF Number ID] [int] NULL ,
> [Month Number] [smallint] NULL ,
> CONSTRAINT [PK_Call Counts] PRIMARY KEY CLUSTERED
> (
> [Call Count ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Call Counts_RCF Numbers] FOREIGN KEY
> (
> [RCF Number ID]
> ) REFERENCES [RCF Numbers] (
> [RCF Number ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
> GO
> ################################
> Here's a view to query the correct value:
> CREATE VIEW dbo.vwCalcMonthsReported
> AS
> SELECT MAX(dbo.[Call Counts].[Month Number]) AS [Months of Data],
> dbo.Versions.[Number Months Reported], dbo.[RCF Numbers].[Version ID]
> FROM dbo.Versions INNER JOIN
> dbo.[RCF Numbers] ON dbo.Versions.[Version ID] =
> dbo.[RCF Numbers].[Version ID] INNER JOIN
> dbo.[Call Counts] ON dbo.[RCF Numbers].[RCF Number I
D]
> = dbo.[Call Counts].[RCF Number ID]
> GROUP BY dbo.Versions.[Number Months Reported], dbo.[RCF Numbers].[Version
ID]
> HAVING (MAX(dbo.[Call Counts].[Month Number]) > 3)
> GO
> ################################
> Here's my stored procedure:
> CREATE PROCEDURE dbo.sp_Update_Months_Reported
> AS UPDATE dbo.vwCalcMonthsReported
> SET [Number Months Reported] = [Months of Data] - 3
> GO
> #####################
>|||On Thu, 7 Apr 2005 08:37:03 -0700, mike wrote:

>I have three tables, [Versions], [RCF Numbers] and [Call Counts]. [Versions]
>is in a one to many relationship with [RCF Numbers], and [RCF Numbers] is o
ne
>to many with [Call Counts].
>I need to update [Versions].[Number Months Reported] with the highest value
>in [Call Counts].[Month Number] (technically, the highest value 3), but
>when I write a stored procedure to do this I get an error saying the
>procedure is not updateable because the underlying query contains aggregate
s.
>Is there a way around this?
Hi Mike,
It's hard to say if this will work without having any sample data to
test it on (see www.aspfaq.com/5006), but you might try if this update
statement does the trick:
UPDATE dbo.Versions
SET [Number Months Reported] =
(SELECT MAX(dbo.[Call Counts].[Month Number])
FROM dbo.[RCF Numbers] AS rn
INNER JOIN dbo.[Call Counts] AS cc
ON cc.[RCF Number ID] = rn.[RCF Number ID]
WHERE rn.[Version ID] = dbo.Versions.[Version ID]) - 3
WHERE (SELECT MAX(dbo.[Call Counts].[Month Number])
FROM dbo.[RCF Numbers] AS rn
INNER JOIN dbo.[Call Counts] AS cc
ON cc.[RCF Number ID] = rn.[RCF Number ID]
WHERE rn.[Version ID] = dbo.Versions.[Version ID]) > 3
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo. I needed to tweak it a bit, but your sp works great!
"Hugo Kornelis" wrote:

> On Thu, 7 Apr 2005 08:37:03 -0700, mike wrote:
>
> Hi Mike,
> It's hard to say if this will work without having any sample data to
> test it on (see www.aspfaq.com/5006), but you might try if this update
> statement does the trick:
> UPDATE dbo.Versions
> SET [Number Months Reported] =
> (SELECT MAX(dbo.[Call Counts].[Month Number])
> FROM dbo.[RCF Numbers] AS rn
> INNER JOIN dbo.[Call Counts] AS cc
> ON cc.[RCF Number ID] = rn.[RCF Number ID]
> WHERE rn.[Version ID] = dbo.Versions.[Version ID]) - 3
> WHERE (SELECT MAX(dbo.[Call Counts].[Month Number])
> FROM dbo.[RCF Numbers] AS rn
> INNER JOIN dbo.[Call Counts] AS cc
> ON cc.[RCF Number ID] = rn.[RCF Number ID]
> WHERE rn.[Version ID] = dbo.Versions.[Version ID]) > 3
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Aggregate First Not Available?

The following query runs on Access and returns a result table that removes
duplicate account numbers even if they have different names.
SELECT AcctNum, First(t.AccountName) AS AccountName
FROM tblAcctChart t
GROUP BY AcctNum
ORDER BY AcctNum;
101 N1
101 N2
101 N3
results in
101 N1
I am feeling real dumb about this. First is not an aggregate function
available on SQL Server 2000. However, I know a way to accomplish the same
thing exists on SQL Server. I am having a serious senior moment and cannot
work my way through the fog to put one together.
Would some kind soul please give me a query that will run on SQL Server
2000 that will accomplish the same task.
Thanks.
MikeThere is no native ordering of rows in a table, so the meaning of "first"
depends on what criteria you use to pick to one AccountName that survives.
To pick the first name alphabetically:
SELECT AcctNum, MIN(AccountName) AS AccountName
FROM tblAccChart
GROUP BY AcctNum
ORDER BY AccNum
"MikeV06" wrote:

> The following query runs on Access and returns a result table that removes
> duplicate account numbers even if they have different names.
> SELECT AcctNum, First(t.AccountName) AS AccountName
> FROM tblAcctChart t
> GROUP BY AcctNum
> ORDER BY AcctNum;
> 101 N1
> 101 N2
> 101 N3
> results in
> 101 N1
> I am feeling real dumb about this. First is not an aggregate function
> available on SQL Server 2000. However, I know a way to accomplish the same
> thing exists on SQL Server. I am having a serious senior moment and cannot
> work my way through the fog to put one together.
> Would some kind soul please give me a query that will run on SQL Server
> 2000 that will accomplish the same task.
> Thanks.
> Mike
>|||You either need to use MIN or TOP, depending on what you want.
MIN(t.AccountName) will give you the first account name, alphabetically for
each AcctNum.
select top 1 from columnList
order by columnList
will return only the first row based on your order by clause.
"MikeV06" <me@.privacy.net> wrote in message
news:1t6yt3o7yvb6w.dlg@.mycomputer06.invalid.com...
> The following query runs on Access and returns a result table that removes
> duplicate account numbers even if they have different names.
> SELECT AcctNum, First(t.AccountName) AS AccountName
> FROM tblAcctChart t
> GROUP BY AcctNum
> ORDER BY AcctNum;
> 101 N1
> 101 N2
> 101 N3
> results in
> 101 N1
> I am feeling real dumb about this. First is not an aggregate function
> available on SQL Server 2000. However, I know a way to accomplish the same
> thing exists on SQL Server. I am having a serious senior moment and cannot
> work my way through the fog to put one together.
> Would some kind soul please give me a query that will run on SQL Server
> 2000 that will accomplish the same task.
> Thanks.
> Mike|||MikeV06 wrote:
> The following query runs on Access and returns a result table that removes
> duplicate account numbers even if they have different names.
> SELECT AcctNum, First(t.AccountName) AS AccountName
> FROM tblAcctChart t
> GROUP BY AcctNum
> ORDER BY AcctNum;
> 101 N1
> 101 N2
> 101 N3
> results in
> 101 N1
> I am feeling real dumb about this. First is not an aggregate function
> available on SQL Server 2000. However, I know a way to accomplish the same
> thing exists on SQL Server. I am having a serious senior moment and cannot
> work my way through the fog to put one together.
> Would some kind soul please give me a query that will run on SQL Server
> 2000 that will accomplish the same task.
> Thanks.
> Mike
Use MIN or MAX or come up with a better definition of what you mean by
"first". The Access query you posted actually returns a random result
for the value of AccountName, which may not be a good idea in many
cases.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> I am feeling real dumb about this. First is not an aggregate function
> available on SQL Server 2000.
Because "FIRST" does not make sense. First what? Physical row? Tables
are, by definition, an unordered set of rows. So the concepts of FIRST,
LAST, or 23rd row make absolutely no sense unless you give more context. As
others have noted, you can order alphabetically by using the aggregate
function MIN(t.AccountName).
I have a brief bit on this here (about 80% of the way down), but in general
the article may be useful to you as well:
http://www.aspfaq.com/2214
A|||My thanks to all who replied. Of course, Min/Max -- blind spot zapping me
broadside. I was overlooking the obvious and trying to make it difficult.
Arggg... Thanks again.
On Wed, 17 May 2006 13:46:01 -0700, Mark Williams wrote:
> There is no native ordering of rows in a table, so the meaning of "first"
> depends on what criteria you use to pick to one AccountName that survives.
> To pick the first name alphabetically:
> SELECT AcctNum, MIN(AccountName) AS AccountName
> FROM tblAccChart
> GROUP BY AcctNum
> ORDER BY AccNum
> "MikeV06" wrote:
>|||On Wed, 17 May 2006 17:05:46 -0400, Aaron Bertrand [SQL Server MVP] wrote:

> Because "FIRST" does not make sense. First what? Physical row? Tables
> are, by definition, an unordered set of rows. So the concepts of FIRST,
> LAST, or 23rd row make absolutely no sense unless you give more context.
As
> others have noted, you can order alphabetically by using the aggregate
> function MIN(t.AccountName).
> I have a brief bit on this here (about 80% of the way down), but in genera
l
> the article may be useful to you as well:
> http://www.aspfaq.com/2214
> A
Very useful indeed. Thank you for the url.