error(2)

create #table(A nvarchar(30), B nvarchar(20), c
nvarchar(15), D int)
insert into #table
select a,b,c,d
from x,y,z where c like 'Norm%'
group by a,b,c,d

create #table(class,name, exam,score) as:
A name1 math 100
A name1 math 88
A name1 Phy 98
A name1 Chm 98
A name1 SPT 89
A name2 math 54
A name2 math 79
A name2 Phy 79
A name2 Chm 44
A name2 SPT 34
B name1 math 54
B name1 math 23
B name1 Phy 54
B name1 Chm 98
B name1 SPT 89
B name2 math 35
B name2 math 35
B name2 Phy 33
B name2 Chm 66
B name2 SPT 12

Finding max(score) for exam started with 'm'
grouped by class and name.

select *,
(select max(result) from #table T where T.class =
D.class and
T.name = D.name and T.exame like 'm%' ) as mx

from #table D
DROP TABLE #table
GO

NOw, if there is no data inserted into #table , got:
not supported error message otherwise it works OK.
Do not like to get this error message when no data
is inserted into #table. how?.

If exists(select *,
(select max(result) from #table T where T.class =
D.class and
T.name = D.name and T.exame like 'm%' ) as mx
from #table D)

Did not resolve it.

--
Sent by from yahoo subdomain of com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.comu359375000@.spawnkill.ip-mobilphone.net (alexsql2000@.yahoo.com) wrote in message news:<l.1066087533.1806060791@.[63.127.215.130]>...
> create #table(A nvarchar(30), B nvarchar(20), c
> nvarchar(15), D int)
> insert into #table
> select a,b,c,d
> from x,y,z where c like 'Norm%'
> group by a,b,c,d
>
> create #table(class,name, exam,score) as:
> A name1 math 100
> A name1 math 88
> A name1 Phy 98
> A name1 Chm 98
> A name1 SPT 89
> A name2 math 54
> A name2 math 79
> A name2 Phy 79
> A name2 Chm 44
> A name2 SPT 34
> B name1 math 54
> B name1 math 23
> B name1 Phy 54
> B name1 Chm 98
> B name1 SPT 89
> B name2 math 35
> B name2 math 35
> B name2 Phy 33
> B name2 Chm 66
> B name2 SPT 12
> Finding max(score) for exam started with 'm'
> grouped by class and name.
> select *,
> (select max(result) from #table T where T.class =
> D.class and
> T.name = D.name and T.exame like 'm%' ) as mx
> from #table D
> DROP TABLE #table
> GO
>
> NOw, if there is no data inserted into #table , got:
> not supported error message otherwise it works OK.
> Do not like to get this error message when no data
> is inserted into #table. how?.
> If exists(select *,
> (select max(result) from #table T where T.class =
> D.class and
> T.name = D.name and T.exame like 'm%' ) as mx
> from #table D)
> Did not resolve it.

Your explanation isn't completely clear (your query doesn't match your
column names, and you don't give the exact error message you're
getting), but it looks like this should simply work:

select class, name, exam, max(score)
from #table
where exam like 'm%'
group by class, name, exam

If this isn't what you need, then consider posting working CREATE
TABLE statements, along with INSERTs to add your sample data, and the
output you expect.

Simon|||Hi

The following are untested by you may want to try:

If exists(select * from #table T where T.exame like 'm%' )
select *, (select max(T.result)
from #table T
where T.class = D.class
and T.name = D.name
and exame like 'm%' ) as mx
from #table D

Or try the following without the check:
select D.*, T.Mx
from #table D JOIN

(select Class, Name, max(result) as MX
from #table
WHERE exame like 'm%'
GROUP BY Class, Name ) T ON T.class = D.class
and T.name = D.name
WHERE D.exame like 'm%'

John

"alexsql2000@.yahoo.com" <u359375000@.spawnkill.ip-mobilphone.net> wrote in
message news:l.1066087533.1806060791@.[63.127.215.130]...
> create #table(A nvarchar(30), B nvarchar(20), c
> nvarchar(15), D int)
> insert into #table
> select a,b,c,d
> from x,y,z where c like 'Norm%'
> group by a,b,c,d
>
> create #table(class,name, exam,score) as:
> A name1 math 100
> A name1 math 88
> A name1 Phy 98
> A name1 Chm 98
> A name1 SPT 89
> A name2 math 54
> A name2 math 79
> A name2 Phy 79
> A name2 Chm 44
> A name2 SPT 34
> B name1 math 54
> B name1 math 23
> B name1 Phy 54
> B name1 Chm 98
> B name1 SPT 89
> B name2 math 35
> B name2 math 35
> B name2 Phy 33
> B name2 Chm 66
> B name2 SPT 12
> Finding max(score) for exam started with 'm'
> grouped by class and name.
> select *,
> (select max(result) from #table T where T.class =
> D.class and
> T.name = D.name and T.exame like 'm%' ) as mx
> from #table D
> DROP TABLE #table
> GO
>
> NOw, if there is no data inserted into #table , got:
> not supported error message otherwise it works OK.
> Do not like to get this error message when no data
> is inserted into #table. how?.
> If exists(select *,
> (select max(result) from #table T where T.class =
> D.class and
> T.name = D.name and T.exame like 'm%' ) as mx
> from #table D)
> Did not resolve it.
>
>
> --
> Sent by from yahoo subdomain of com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com

error(2)

create #table(A nvarchar(30), B nvarchar(20), c
nvarchar(15), D int)
insert into #table
select a,b,c,d
from x,y,z where c like 'Norm%'
group by a,b,c,d
create #table(class,name, exam,score) as:
A name1 math 100
A name1 math 88
A name1 Phy 98
A name1 Chm 98
A name1 SPT 89
A name2 math 54
A name2 math 79
A name2 Phy 79
A name2 Chm 44
A name2 SPT 34
B name1 math 54
B name1 math 23
B name1 Phy 54
B name1 Chm 98
B name1 SPT 89
B name2 math 35
B name2 math 35
B name2 Phy 33
B name2 Chm 66
B name2 SPT 12
Finding max(score) for exam started with 'm'
grouped by class and name.
select *,
(select max(result) from #table T where T.class = D.class and
T.name = D.name and T.exame like 'm%' ) as mx
from #table D
DROP TABLE #table
GO
NOw, if there is no data inserted into #table , got:
not supported error message otherwise it works OK.
Do not like to get this error message when no data
is inserted into #table. how?.
If exists(select *,
(select max(result) from #table T where T.class = D.class and
T.name = D.name and T.exame like 'm%' ) as mx
from #table D)
Did not resolve it.
--
Sent by from yahoo subdomain of com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.comu359375000@.spawnkill.ip-mobilphone.net (alexsql2000@.yahoo.com) wrote in message news:<l.1066087533.1806060791@.[63.127.215.130]>...
> create #table(A nvarchar(30), B nvarchar(20), c
> nvarchar(15), D int)
> insert into #table
> select a,b,c,d
> from x,y,z where c like 'Norm%'
> group by a,b,c,d
>
> create #table(class,name, exam,score) as:
> A name1 math 100
> A name1 math 88
> A name1 Phy 98
> A name1 Chm 98
> A name1 SPT 89
> A name2 math 54
> A name2 math 79
> A name2 Phy 79
> A name2 Chm 44
> A name2 SPT 34
> B name1 math 54
> B name1 math 23
> B name1 Phy 54
> B name1 Chm 98
> B name1 SPT 89
> B name2 math 35
> B name2 math 35
> B name2 Phy 33
> B name2 Chm 66
> B name2 SPT 12
> Finding max(score) for exam started with 'm'
> grouped by class and name.
> select *,
> (select max(result) from #table T where T.class => D.class and
> T.name = D.name and T.exame like 'm%' ) as mx
> from #table D
> DROP TABLE #table
> GO
>
> NOw, if there is no data inserted into #table , got:
> not supported error message otherwise it works OK.
> Do not like to get this error message when no data
> is inserted into #table. how?.
> If exists(select *,
> (select max(result) from #table T where T.class => D.class and
> T.name = D.name and T.exame like 'm%' ) as mx
> from #table D)
> Did not resolve it.
Your explanation isn't completely clear (your query doesn't match your
column names, and you don't give the exact error message you're
getting), but it looks like this should simply work:
select class, name, exam, max(score)
from #table
where exam like 'm%'
group by class, name, exam
If this isn't what you need, then consider posting working CREATE
TABLE statements, along with INSERTs to add your sample data, and the
output you expect.
Simon|||Hi
The following are untested by you may want to try:
If exists(select * from #table T where T.exame like 'm%' )
select *, (select max(T.result)
from #table T
where T.class = D.class
and T.name = D.name
and exame like 'm%' ) as mx
from #table D
Or try the following without the check:
select D.*, T.Mx
from #table D JOIN
(select Class, Name, max(result) as MX
from #table
WHERE exame like 'm%'
GROUP BY Class, Name ) T ON T.class = D.class
and T.name = D.name
WHERE D.exame like 'm%'
John
"alexsql2000@.yahoo.com" <u359375000@.spawnkill.ip-mobilphone.net> wrote in
message news:l.1066087533.1806060791@.[63.127.215.130]...
> create #table(A nvarchar(30), B nvarchar(20), c
> nvarchar(15), D int)
> insert into #table
> select a,b,c,d
> from x,y,z where c like 'Norm%'
> group by a,b,c,d
>
> create #table(class,name, exam,score) as:
> A name1 math 100
> A name1 math 88
> A name1 Phy 98
> A name1 Chm 98
> A name1 SPT 89
> A name2 math 54
> A name2 math 79
> A name2 Phy 79
> A name2 Chm 44
> A name2 SPT 34
> B name1 math 54
> B name1 math 23
> B name1 Phy 54
> B name1 Chm 98
> B name1 SPT 89
> B name2 math 35
> B name2 math 35
> B name2 Phy 33
> B name2 Chm 66
> B name2 SPT 12
> Finding max(score) for exam started with 'm'
> grouped by class and name.
> select *,
> (select max(result) from #table T where T.class => D.class and
> T.name = D.name and T.exame like 'm%' ) as mx
> from #table D
> DROP TABLE #table
> GO
>
> NOw, if there is no data inserted into #table , got:
> not supported error message otherwise it works OK.
> Do not like to get this error message when no data
> is inserted into #table. how?.
> If exists(select *,
> (select max(result) from #table T where T.class => D.class and
> T.name = D.name and T.exame like 'm%' ) as mx
> from #table D)
> Did not resolve it.
>
>
> --
> Sent by from yahoo subdomain of com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com

Error with the trigger

I made a trigger for delete just like this.

"
CREATE TRIGGER [MbPromoHdrDel] ON [dbo].[MbPromo_hdr]
FOR DELETE
AS

Declare @.severity int,
@.IdNmbr nvarchar(10)
Set @.Severity = 0

Declare NoId Cursor Local Static for
Select [Promo_Id] from deleted
Open NoId
While 1=1
Begin
Fetch NoId Into
@.IdNmbr

If @.@.Fetch_Status <> 0
Break

If Exists (Select 1 from MbPromo_dtl where [Promo_Id]=@.IdNmbr)
Set @.Severity = @.Severity+1
Else
Begin
Delete From MbPromo_hdr where [Promo_Id]=@.IdNmbr
End
End
Close NoId
Deallocate NoId

If @.Severity = 0
Commit
Else
Begin
RollBack
Print 'Data Cannot Be delete'
End
Go
"

When i delete the record from Enterprise manager it give me an error

"Another user has modified the content of this table or view. The database
row you are modifying no longer exists in the database."

Why? And it happen with all of the record at my table

--
Message posted via http://www.sqlmonster.comAvoid cursors at any time, buit especially in triggers.

I don't understand the purpose of the DELETE statement here. This is an
AFTER trigger so the row has already been deleted. Also, why are you
using a trigger to check for dependent rows? It seems like you could do
that more easily with a foreign key.

If Promo_id is unique in MbPromo_hdr then your trigger could be
rewritten as:

CREATE TRIGGER MbPromoHdrDel ON dbo.MbPromo_hdr FOR DELETE
AS
IF EXISTS
(SELECT *
FROM deleted AS D
JOIN mbpromo_dtl AS M
ON D.promo_id = M.promo_id)
BEGIN
ROLLBACK TRAN
RAISERROR('Data cannot be deleted',16,1)
END

but a foreign key would be a much better solution.

--
David Portas
SQL Server MVP
--|||Michael Teja via SQLMonster.com (forum@.SQLMonster.com) writes:
> CREATE TRIGGER [MbPromoHdrDel] ON [dbo].[MbPromo_hdr]
> FOR DELETE
> AS
> Declare @.severity int,
> @.IdNmbr nvarchar(10)
> Set @.Severity = 0
>
> Declare NoId Cursor Local Static for
> Select [Promo_Id] from deleted
> Open NoId
> While 1=1
> Begin
> Fetch NoId Into
> @.IdNmbr
> If @.@.Fetch_Status <> 0
> Break
> If Exists (Select 1 from MbPromo_dtl where [Promo_Id]=@.IdNmbr)
> Set @.Severity = @.Severity+1
> Else
> Begin
> Delete From MbPromo_hdr where [Promo_Id]=@.IdNmbr
> End
> End

In addition to David's comments: this would make a little more sense,
if you had an INSTEAD OF trigger. Rather than having an AFTER trigger
that rolls back a large DELETE in case of error, an INSTEAD OF trigger
can check for conditions before hand, but must then also carry out the
original action.

However, there is still no reason to do this one-by-one, and referential
constraints are better to use for this.

> If @.Severity = 0
> Commit

And this is something you should not do in a trigger! If you commit within
a trigger (and the trigger does not have a matching BEGIN TRANSACTION),
you create an error situation which causes the terminattion of the batch
when the trigger exits, so subsequent statments are not executed. In
SQL 2000 there is no error message actually printed, it all happens
internally in the server. SQL 2005 will give an error message for this.

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

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

It work.

--
Message posted via http://www.sqlmonster.com