Interesting UPDATE STATEMENT for SQL

This Following statement executes perfectly in SQL2000
but not in SQL7.0 it gives the message
Server: Msg 147, Level 16, State 2, Procedure spTEST1, Line 57
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Create Procedure "spTEST1"
As

--Update Processed Flags
UPDATE tblTransaction
SET TransProcessed = 1
FROM dbo.tblTrustGroupTrust
INNER JOIN dbo.tblTrust ON dbo.tblTrustGroupTrust.TgtTrustID = dbo.tblTrust.TrustID
INNER JOIN dbo.tblTransaction ON dbo.tblTrust.TrustID = dbo.tblTransaction.TransTrustID
INNER JOIN dbo.tblShareholder ON dbo.tblTransaction.TransShID = dbo.tblShareholder.ShID
INNER JOIN dbo.tblTransType ON dbo.tblTransType.TransTypeID = dbo.tblTransaction.TransTypeID
WHERE (dbo.tblTrustGroupTrust.TgtTrustGroupID = 3) AND (dbo.tblTransaction.TransProcessed = 0)
AND (dbo.tblShareholder.ShPaymentMethod = 1) AND (dbo.tblShareholder.ShDeceased = 0) AND tblShareholder.ShBankAccount IS NOT NULL
AND EXISTS
(
SELECT dbo.tblTransaction.TransShID
FROM dbo.tblTrustGroupTrust
INNER JOIN dbo.tblTransaction ON dbo.tblTrustGroupTrust.tgtTrustID = dbo.tblTransaction.TransTrustID
WHERE (dbo.tblTransaction.TransProcessed = 0) AND (dbo.tblTrustGroupTrust.TgtTrustGroupID = 3) AND (dbo.tblShareholder.SHID = dbo.tblTransaction.TransShID)
GROUP BY dbo.tblTransaction.TransShID
HAVING SUM(tblTransaction.TransAmt) >= 10
)

When using a straight select on this statement in SQL7.0 it works fine:
SELECT tblTransaction.TransProcessed
FROM .....

Can anyone shed some light on this?The aggregate is on the table being updated - although it is in a subquery this is correllated and so still fails the test.

If you can change the having clause to
HAVING SUM(dbo.tblTransaction.TransAmt) >= 10

so that it is using the copy of the table in the subquery then it should work.|||It might be clearer if you use an alias for the subquery table like
tblTransaction t2.|||Thanks, I very nearly tried to add the missing dbo prefix's in but I presumed that would be too trivial and the error was more complicated than something like that.

Obviously SQL2000 is a little more relaxed on this sort of thing, as it works without error.

Interesting SQL Server behaviour

Hi,
The following code describes a problem I'm having when I'm selecting
data via a Case statement.
The data I'm selecting is from a table with 2 varchar columns and 1
int column. When I select this data I get the following error:
Server: Msg245, Level 16,State 1, Line 1
Syntax error converting the varchar values 'A' to a column of datatype
int.
If I cast the int to a varchar it works, but I would prefer not to
cast if possible. SQL server seems to look at all the datatypes in the
case statement and if a int data type appears, it seems to be trying
to insert into a int column. Is this some sort of temp table? Anyone
know why it assigned an int column here?
Any ideas what I can do
Thanks,
Tom
- --
- -- Set up the table
- --
create table Tom
(charcol1 varchar(2)
,charcol2 varchar(2)
,intcol1 int
)
insert into tom
(charcol1
,intcol1
,charcol2
)
values
('A',1,'C')
- ---
- -- Code
- ---
SELECT
CASE numlist.list
WHEN 1 THEN
Tom.charcol1
WHEN 2 THEN
--cast (Tom.intcol1 as varchar(5))
Tom.intcol1
WHEN 3 THEN
Tom.charcol2
END AS result
FROM Tom
CROSS JOIN (select 1 as list
union all
select 2 as list
union all
select 3 as list
) as numlistThis is how CASE work, result from CASE expression is of the same datatype.
Output is determined as
per "datatype precedence" documented in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Staggly" <tomstagg@.gmail.com> wrote in message
news:e646e15f.0502090622.8a26408@.posting.google.com...
> Hi,
> The following code describes a problem I'm having when I'm selecting
> data via a Case statement.
> The data I'm selecting is from a table with 2 varchar columns and 1
> int column. When I select this data I get the following error:
> Server: Msg245, Level 16,State 1, Line 1
> Syntax error converting the varchar values 'A' to a column of datatype
> int.
> If I cast the int to a varchar it works, but I would prefer not to
> cast if possible. SQL server seems to look at all the datatypes in the
> case statement and if a int data type appears, it seems to be trying
> to insert into a int column. Is this some sort of temp table? Anyone
> know why it assigned an int column here?
> Any ideas what I can do
> Thanks,
> Tom
> - --
> - -- Set up the table
> - --
> create table Tom
> (charcol1 varchar(2)
> ,charcol2 varchar(2)
> ,intcol1 int
> )
> insert into tom
> (charcol1
> ,intcol1
> ,charcol2
> )
> values
> ('A',1,'C')
> - ---
> - -- Code
> - ---
> SELECT
> CASE numlist.list
> WHEN 1 THEN
> Tom.charcol1
> WHEN 2 THEN
> --cast (Tom.intcol1 as varchar(5))
> Tom.intcol1
> WHEN 3 THEN
> Tom.charcol2
> END AS result
> FROM Tom
> CROSS JOIN (select 1 as list
> union all
> select 2 as list
> union all
> select 3 as list
> ) as numlist

Integrity Check Fails

I setup a DB Maint, Plan for several databases however,
the Integrity Check job fails for a few of my databases.
The error is "Repair statement not processed. Database
needs to be in single user mode."
Any thoughts on why this is happening?
Thanks,
DonPretty much what it says. What version and service pack of SQL Server? Note that you can't put
master in single user mode. I recommend that you remove that darn option to "attempt to repair minor
problems". :-)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Don" <drduquette@.aol.com> wrote in message news:000001c36ca4$732448e0$a101280a@.phx.gbl...
> I setup a DB Maint, Plan for several databases however,
> the Integrity Check job fails for a few of my databases.
> The error is "Repair statement not processed. Database
> needs to be in single user mode."
> Any thoughts on why this is happening?
> Thanks,
> Don|||Thanks for the quick response! We are using SQLserver
2000 service pack 3. I realize that I could uncheck the
repair but that defeats the purpose if there is a problem
with the database. In addition, MS recommends that the
repair option be checked.
Is there any work around to this issue of putting the DB
in single user mode?
Thanks,
Don
>--Original Message--
>Pretty much what it says. What version and service pack
of SQL Server? Note that you can't put
>master in single user mode. I recommend that you remove
that darn option to "attempt to repair minor
>problems". :-)
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
>"Don" <drduquette@.aol.com> wrote in message
news:000001c36ca4$732448e0$a101280a@.phx.gbl...
>> I setup a DB Maint, Plan for several databases however,
>> the Integrity Check job fails for a few of my databases.
>> The error is "Repair statement not processed. Database
>> needs to be in single user mode."
>> Any thoughts on why this is happening?
>> Thanks,
>> Don
>
>.
>|||Don,
Tibor mentioned a restriction on master. In addition, if a database has any
open connections it cannot be changed to single user mode unless you use the
WITH ROLLBACK clauses.
ALTER DATABASE mydatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
This breaks all unqualified connections and switches to single user mode.
(Much easier than the old method of writing a looping procedure to KILL
connections until they were finally all gone.)
Russell Fields
"Don" <drduquette@.aol.com> wrote in message
news:0e9901c36ca7$0659c2f0$a301280a@.phx.gbl...
> Thanks for the quick response! We are using SQLserver
> 2000 service pack 3. I realize that I could uncheck the
> repair but that defeats the purpose if there is a problem
> with the database. In addition, MS recommends that the
> repair option be checked.
> Is there any work around to this issue of putting the DB
> in single user mode?
> Thanks,
> Don
> >--Original Message--
> >Pretty much what it says. What version and service pack
> of SQL Server? Note that you can't put
> >master in single user mode. I recommend that you remove
> that darn option to "attempt to repair minor
> >problems". :-)
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> >"Don" <drduquette@.aol.com> wrote in message
> news:000001c36ca4$732448e0$a101280a@.phx.gbl...
> >> I setup a DB Maint, Plan for several databases however,
> >> the Integrity Check job fails for a few of my databases.
> >> The error is "Repair statement not processed. Database
> >> needs to be in single user mode."
> >>
> >> Any thoughts on why this is happening?
> >>
> >> Thanks,
> >> Don
> >
> >
> >.
> >|||The purpose of CHECKDB is to get *notified* in the unlikely event of a problem. I surely don't want
some background thing try to repair the database if I run into a problem with it. I want to be
there, do a log backup first, think, etc etc etc.
Anyhow, master cannot be in single user mode, if this is the one which is causing your problem, then
you have to remove it from the plan, or don't do background repair.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Don" <drduquette@.aol.com> wrote in message news:0e9901c36ca7$0659c2f0$a301280a@.phx.gbl...
> Thanks for the quick response! We are using SQLserver
> 2000 service pack 3. I realize that I could uncheck the
> repair but that defeats the purpose if there is a problem
> with the database. In addition, MS recommends that the
> repair option be checked.
> Is there any work around to this issue of putting the DB
> in single user mode?
> Thanks,
> Don
> >--Original Message--
> >Pretty much what it says. What version and service pack
> of SQL Server? Note that you can't put
> >master in single user mode. I recommend that you remove
> that darn option to "attempt to repair minor
> >problems". :-)
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> >"Don" <drduquette@.aol.com> wrote in message
> news:000001c36ca4$732448e0$a101280a@.phx.gbl...
> >> I setup a DB Maint, Plan for several databases however,
> >> the Integrity Check job fails for a few of my databases.
> >> The error is "Repair statement not processed. Database
> >> needs to be in single user mode."
> >>
> >> Any thoughts on why this is happening?
> >>
> >> Thanks,
> >> Don
> >
> >
> >.
> >